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?
1
-
4You 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– Simon P Stevens2009-12-19 00:51:33 +00:00Commented Dec 19, 2009 at 0:51
Add a comment
|
3 Answers
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.)
4 Comments
leora
wouldn't i still be passing in a string into the parameterized query and have the same issue ?
marc_s
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)
LukeH
@marc_s: I've updated the answer to use the safe-in-all-circumstances format.
Lasse V. Karlsen
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)
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.
Comments
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
