what is the correct way to format a datetime in SQL server datetime field

Question

I have a dateTime object in C# and i want to do an insert into SQL Server datetime field. What is the correct format for this?

Answer 1

The correct way to do this is by using a parameterised query, not text formatting. Then you can just use a strongly-typed SqlDbType.DateTime parameter.

(If you absolutely must use text formatting to do this - and I strongly recommend against it - then something like yyyyMMdd HH:mm:ss should do the trick.)

Answer 2

To expand on @Luke's answer I came across this bug just the other day.

The yyyy-MM-dd HH:mm:ss format has a locale/language issue on SQL Server 2005 (an example is French), but is fixed in SQL 2008:

So, do NOT use this format: yyyy-MM-dd HH:mm:ss (space separator).

Only use: yyyy-MM-ddTHH:mm:ss ("T" separator) or yyyyMMdd HH:mm:ss (no dash delimiters)

Important if you're generating scripts that include datetime constants.

See Jamie Thomson's article on SQL Blog