Solution to The conversion of a char data type to a datetime data type resulted in an out-of-range datetime

Hi,

I would like to share my opinion on one of the problems i faced while working with datetime format in Asp.net 2.0 and sql server 2000 on windows server 2003.

I encountered an error 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime'. while trying to save a date to the datetime column in database.

The Reason : Sql server supports the datetime format depending upon the regional datetime setting of the database server. so if your development server supposet MM/dd/yyyy , not necessarily your production or testing database server will support the same format. As a result you might have done coding to save it in a particular format & the same code may not work on another server.

Solution : After trying various things i came to a conclusion the best way to handle this error is to have precautionary steps while dealing with them.

Consider a case, where you just want to store the date.. without timestamp. as sql server doesnt have any specific date format you need to save the date in datetime dtatype column in database.
You can first create a stored procedure with the insert statement

CREATE PROCEDURE [dbo].[spInsert_]
@dt datetime
AS
BEGIN
Insert into IDtable (dt) values (@dt);
GO
END

Now call the above stored procedure from front end asp.net 2.0 code.

Dim dt As DateTime = DateTime.Now.ToString("yyyy-MM-dd")
MyCmd.Connection = MyCon
MyCmd.CommandType = Data.CommandType.StoredProcedure
MyCmd.CommandText = "spInsert_"
MyCmd.Parameters.Add("@dt", SqlDbType.DateTime)
MyCmd.Parameters("@dt").Value = dt
MyCmd.Transaction = trans
Dim res As Integer = MyCmd.ExecuteNonQuery()

Here, I would like to point out, the universal format supported by sql server is yyyy-MM-dd hh:nn:ss where nn is minutes...

so if you make a practice of storing your data in the said format, you will never encounter this error...

second important thing is to use Store Procedure with parameters, you can just pass the date value as parameter to store procedure, it will do the necessary casting.

Other solution :
SET DATEFORMAT dmy
before executing any sql statement. (i havent tried this one)

Source : http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/095eb7a7-222a-4a15-8301-2a2acca2ca3f/

if you find my article useful, do leave a comment.

Cheers,
happy programming
Idu