7

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?

leora
  • 188,729
  • 360
  • 878
  • 1,366
  • 4
    You probably shouldn't be building up SQL strings in code. Instead you should look at using either stored procs, parameterized queries or an ORM. Any of these would resolve your problem with formatting as you just pass the object, you don't have to do any string formatting. – Simon P Stevens Dec 19 '09 at 00:51

3 Answers3

16

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

LukeH
  • 263,068
  • 57
  • 365
  • 409
  • wouldn't i still be passing in a string into the parameterized query and have the same issue ? – leora Dec 19 '09 at 03:58
  • Actually, the format that is guaranteed to work on any SQL Server regardless of language and regional settings would be `yyyyMMdd HH:mm:ss` (without the dashes - those caused trouble in certain language settings) – marc_s Dec 19 '09 at 08:00
  • @marc_s: I've updated the answer to use the safe-in-all-circumstances format. – LukeH Dec 23 '09 at 00:07
  • Try to query, with the .net classes a table indexed on a DateTime column using a BETWEEN dt1 AND dt2 criteria, and you'll quickly find a use for either query hints or inline values, both of which require the correct formatting (hint: the query optimizer isn't always smart enough to figure out that you only want a few rows, tablescan here we go) – Lasse V. Karlsen Dec 23 '09 at 00:41
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

devstuff
  • 8,277
  • 1
  • 27
  • 33
0

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
Fredou
  • 19,848
  • 10
  • 58
  • 113