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")
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
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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With