The conversion of a char data type to a datetime data type resulted in an out-of-range datetime
I am putting the current datetime in a microsoft sql server database in the following manner:
System.Data.SqlTypes.SqlDateTime time = new System.Data.SqlTypes.SqlDateTime(DateTime.Now);
sql = "insert myTable (datetime) values ('" + (string) time.ToSqlString() + "')";
SqlCommand cmd = new SqlCommand (sql, conn);
int result = cmd.ExecuteNonQuery();
the column of my table has datatype DATETIME
I tried it 7 times yesterday and today without problem. Now (at 00:45 hours) I get an exception:
the exceptionmessage:
System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I have three questions:
1. how can the date be out-of-range when I use DateTime as the data type?
2. why does it works 7 times and not the eight time?
3. what is the better way to do this?
I did an additional test myself;
I created a table like this:
CREATE testDateTime
(dateTime DATETIME)
and insert:
insert testDateTime (dateTime)
values ('13-8-2005 11:56:39')
this works OK on my local Microsoft SQL Server 2000, but since two days not anymore on my contracters Microsoft SQL Server ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value")
Maybe they had an upgrade with changes in the way datetime values are handled? I sent an email to the administators of my contractors webserver, and I will put a question on a Microsoft SQL Server forum.
When I use a stored procedure to insert a datetime value from .net, it still works, so I won't bother anymore.
 Jan van Casteren wrote: |
| I did an additional test myself; but since two days not anymore on my contracters Microsoft SQL Server ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value")
|
|
This sounds like a regional setting that has changed
In the netherlands 13-1-2005 (ddmmyyyy = 13 jan-2005) is a legal date. but when (on the server) the regional setting is changed to English (mmddyyyy) this is not a legal date because there is no 13th month
Remco
| Jan van Casteren wrote: |
I did an additional test myself;
...
insert testDateTime (dateTime)
values ('13-8-2005 11:56:39')
|
|
It is a good idea in dynamic SQL to force all dates to a format SQL Server is known to accept regardless of regional setting. I think: yyyy-mm-dd hh:nn:ss, or a subset thereof, is safe. (It's been awhile so double check the format)
So what you want would be something like this:
insert testDateTime (dateTime)
values (" & Format(myDateTime, "yyyy-mm-dd hh:nn:ss") & ")"
I have the same problem :( only when Update the Database
When I insert new values, there's no error found !!!
I use 3 combo box to restrict input dd, mm, yyyy. Then store the result in a variable... (DateTime type <-- The same the data field Type in SQL 2000.)
I have checked the System Date Time, the same expected format: dd/mm/yyyy
I haven't found the answer ...
try something like : DateValue("
youDateTimeString here") when inserting or updating
It is, actually, a good idea not to use dynamic SQL at all. It is a straight way to the SQL injection attack. Best way is to use parameterized query and provider will handle formatting for all the types automatically. In this case you do not need to convert anything and just assign value to the parameter as is.
hi
this problem maybe solve by changing the default language of login name in security section.
it must be ENGLISH;
i have 2 user one query run by SA and another run by PA; query run by PA has this exception and solved;
There's only one good way to solve (or rather eliminate) this problem: use parameterized query and forget about formatting issues. Samples can be found on MSDN.
Dear Jan van Casteren,
My client pc also got the same error while inserting data using date picker in the front end VB.net with Sql server 2000 as backend.
Surprisingly, my system is taking and inserting the values perfectly. I dont know, what could be the reason for this error. can u pls suggest me if u got any solution..
Most likely you have different Regional Settings on these PCs and you are assuming that dates always come in specific format. Do not convert date strings into dates inside of the SQL statements. Take date from the control as date type and pass it to the query as a parameter. Do not concatenate it as a string. It will solve the issue
I dont like parameteres, because i coulndt just cut & Paste my complete statement into the SQL Query box to test it
I use DateTime.Now.ToString( "yyyy-MM-dd HH:mm
s" ) that works. Its ISO
