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

use SET DATEFORMAT

a sample took from here

     SET NOCOUNT ON 
     CREATE TABLE X(EXAMPLE INT, D SMALLDATETIME)
     -- EXAMPLE 1
     SET DATEFORMAT MDY
     INSERT INTO X VALUES (1, '10/30/56')
     -- EXAMPLE 2
     SET DATEFORMAT YDM
     INSERT INTO X VALUES (2, '56/31/10')
     -- EXAMPLE 3 
     SET DATEFORMAT YMD
     INSERT INTO X VALUES (3, '56/10/31')
     SELECT * FROM X

Answer 3

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