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.
You should really use parametrized queries for that and pass your DateTime object as a SQL DateTime parameter.
If you parse the DateTime to String you will have to deal with the localisation settings both on your application server as on the database server. That can lead to some nasty surprises like treating the Date as it was in US format on one side and e.g. UK on another. The string 9/12/2000 can be either September the 12th or the 9th of December. So keep the data in the DateTime object/type when exchanging between application and database.
The only time you would parse DateTime to String would be when displaying data (GUI). But then you should make sure to use the proper localization setting when parsing to display it in the format the user is expecting.
The same principle applies to other data types like float for example. The string representation of this varies depending on the locale and I suppose you do not parse float to String when passing it to the database so why do it with the DateTime?
this one won't ever fail:
DateTime.Now.ToString("yyyyMMdd HH:mm:ss.fff")
Watch out if the date is DateTime.Min, as SQL Server uses 1700s for the start of time itself. I'd use an ISO datetime format : DateTime.ToString("s") but I haven't tried that on non-western installs.
e.g.
DateTime.Now.ToString("c")
is
insert into testtable values ('2009-01-22T15:08:13')
Formatting using DateTime.ToString("yyyy-MM-dd HH:mm:ss:fff") will match the MS SQL Server date/time format. (I believe SQL Server is fairly "intelligent" about recognizing slightly different-looking formats, e.g. with slashes, but I've personally used that one successfully.)
EDIT: As noted by commenter, this will probably be ruined in some locales.
