Best way to parse DateTime to SQL server

Question

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")

Solution 1

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

Solution 2

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.

Solution 3

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?

Solution 4

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?

Solution 5

this one won't ever fail: DateTime.Now.ToString("yyyyMMdd HH:mm:ss.fff")

View more solutions

Comments

[Mladen Prajdic]

this one will fail for ydm locale

[kristof]

I am affraid that is not true in the database even the string '1997-12-09' can be treated either as 9th of December or 12th of September depending on the database settings. So you are never guaranteed how the string that you pass to the DB is going to be treated unless you specify the formatting.

[George Mastros]

Kristof, if you are going to use dashes in the format, then you need to use the T. If you remove the dashes, then it will never be mis-interpretted. Instead of '1997-12-09' use '19971209'.

[kristof]

Thanks Mastros - I was not aware of that, I guess you can learn something everyday :)

[kristof]

@SQLMenance - I will leave the first comment even though I was wrong when saying that your answer was not correct.

[MordechayS]

insert into testtable values ('0000-00-00T00:00:00') :)

[Iain]

Should ISO8601 be something like this? yyyy-MM-ddThh:mm:ss.FFF

[Iain]

Above has wrong clock (12-hour). Should be yyyy-MM-ddTHH:mm:ss.FFF