Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to parse DateTime to SQL server

I was wondering what is the best way to parse a DateTime object to your SQL server.

Where you are generating the SQL in code.

I have always used something like DateTime.Now.TolongDateString() and had good results, apart from today where i got a error, and it made me think.

System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string

So what is everyone thoughts and recomendations for a way that will work acrss all SQL server no matter what there setup..

Maybe something like DateTime.Now.ToString("yyyy/MM/dd")

like image 619
TheAlbear Avatar asked Jan 22 '09 14:01

TheAlbear


3 Answers

there are only 2 safe formats

ISO and ISO8601

ISO = yymmdd

ISO8601 = yyyy-mm-dd Thh:mm:ss:mmm(no spaces) (notice the T)

See also here: Setting a standard DateFormat for SQL Server

like image 85
SQLMenace Avatar answered Sep 20 '22 13:09

SQLMenace


Why not parameterise the query and pass the DateTime value as a SQL DateTime input param?

e.g INSERT INTO SomeTable (Blah, MyDateTime) VALUES (1, @MyDateTime)

Then you can be really sure. Even if you're generating the SQL you should be able to handle this specifically?

like image 27
Kev Avatar answered Sep 23 '22 13:09

Kev


I found this specific format was needed:

DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff")

Note casing and absence of any spaces. This is the universal way according to Robyn Page.

like image 21
Netricity Avatar answered Sep 19 '22 13:09

Netricity