Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 3:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 5, 2012 12:53 AM
Points: 234, Visits: 57
Hi All,

We have developed a web appln using ASP.Net 2.0 / Sql Server 2005. In a web form, while inserting a value into a date field , I am getting the following error.

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

We are sending the value in MM/DD/YYYY format from the application.This works in my development environment but not in the production server.

The language assigned to the Sqluser is "English" in both the cases. We don't have access to the production server.

What could be the problem? Kindly throw some light on this.

Thanks in advance
Post #740845
Posted Wednesday, June 24, 2009 3:35 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 615, Visits: 1,261
try
convert(datetime,'date',101)


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #740848
Posted Wednesday, June 24, 2009 3:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 5, 2012 12:53 AM
Points: 234, Visits: 57
Hi,

Thanks for u'r quick reply.

I think you have understood differently. My problem is "while inserting , i am getting the sql exception". But this is happening only in production server.

I can't use the convert function because, i have around 50 pages in my application and have to use this convert function everywhere.

Since it is working in my local server, I think this problem is due to some server settings.

Please tell me where to change this settings...
Post #740852
Posted Wednesday, June 24, 2009 4:04 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 615, Visits: 1,261
Please see the below link

http://support.microsoft.com/kb/173907


-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #740866
Posted Wednesday, June 24, 2009 4:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
A hint for future projects and implementations...

Don't create dynamic SQL statements containing values in your font end. This enables SQL injection and causes problems as your current. Use parameterized statements and you cannot run into those problems.

[code="csharp"]
///=======================================================
// WRONG WAY
using (SqlConnection cn = new SqlConnection("MyConnectionString"))
{
cn.Open();

string statement = "INSERT INTO MyTable (Id, MyDateColumn) VALUES (1, '06/24/2009')";
using (SqlCommand cmd = new SqlCommand(statement, cn))
{
cmd.ExecuteNonQuery();
}
}

///=======================================================
// CORRECT WAY
using (SqlConnection cn = new SqlConnection("MyConnectionString"))
{
cn.Open();

string statement = "INSERT INTO MyTable (Id, MyDateColumn) VALUES (@Id, @MyDate)";
using (SqlCommand cmd = new SqlCommand(statement, cn))
{
SqlParameter paramId = cmd.Parameters.Add("@Id", SqlDbType.Int);
SqlParameter paramMyDate = cmd.Parameters.Add("@MyDate", SqlDbType.DateTime);

paramId.Value = 1;
paramMyDate.Value = DateTime.Now;

cmd.ExecuteNonQuery();
}
}
[/code]

Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #740872
Posted Tuesday, September 25, 2012 3:47 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 321, Visits: 825
Your database login may have a default language different than English, or the production server's default language may not not match the one to your login. Maybe the agent for the job has another default language as well. Either way default languages may not be matching. It is thus best to always use the universal yyyymmdd string format for dates.
Post #1364327
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse