SQL Command Problem 1

[NeilV]

Hi,

I am trying to run the follwoing SQL command using an SQLCommand object,

Insert INTO RearTrace (ID_Production,Date,Barcode,PartNumber,PartDescription) VALUES ('168570',Convert(char,'28/07/2003 16:34:58',13),'PC000001','PC000001','LABEL 85X209.6')

but i get 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.
Int32 ExecuteNonQuery()

If i copy and paste the SQL into SQL server query analyser it runs fine. Can canyone tell me why?

Neil

 

[Titan5]

Try '07/28/2003' instead of '28/07/2003'. Also check your computer's region/language setting. Some countries use mm/dd/yyyy while others may use dd/mm/yyyy or yyyy-mm-dd.

 

[chiph]

If you use ISO-8601 format for your dates, you won't have any problems with their values being recognized. It's yyyy-mm-ddThh-nn-ss (the "T" is a literal). For example, now is 2003-07-29T22:32:00 EDT. You can go a little further and specify the timezone offset, but in most apps that should be stored in a separate field anyway, and you should only store datetimes in GMT (aka UTC).

Chip H.

 

[NeilV]

Cheers guys,

I managed to fix the problem by running the set dateformat command before running my insert query.

Neil