SQL Server - Insert date strings into datetime column using YYYY-MM-DD format

Asked By Scott Bass

27-Mar-09 03:06 AM

SQL Server 2005

Summary:
Is there an option I can set to allow inserting date strings into a
datetime column using the format YYYY-MM-DD?  Reason:  this format is
easier using my ETL software.

Details:
The following is testing I've done with inserting date strings into a
datetime column in my SQL Server 2005 environment:

/*
Testing of date insert using various date strings
Highlight a string (minus the comment delimiter) to execute

Summary of what works:

DD-MM-YYYY, YYYY-DD-MM, DD/MM/YYYY, YYYY/DD/MM, YYYYMMDD, YYMMDD, DD-
MON-YYYY, DD MON YYYY, DD-MON-YY, DD MON YY

Same as above with HH:MM:SS

String types:

=======================================
Date only:

MM-DD-YYYY
DD-MM-YYYY
YYYY-MM-DD
YYYY-DD-MM

MM/DD/YYYY
DD/MM/YYYY
YYYY/MM/DD
YYYY/DD/MM

MM DD YYYY
DD MM YYYY
YYYY MM DD
YYYY DD MM

YYYYMMDD
YYMMDD

YYYYDDMM
YYDDMM

DD-MON-YYYY
DD MON YYYY

DD-MON-YY
DD MON YY

=======================================
Datetime:

Same as above with HH:MM:SS appended

*/

DELETE FROM [dbo].[Scott_Date_Testing]
GO

/* ======================================= */

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03-25-2009')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-03-2009')             --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009-03-25')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009-25-03')             --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03/25/2009')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25/03/2009')             --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009/03/25')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009/25/03')             --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03 25 2009')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 03 2009')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009 03 25')             --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009 25 03')             --NO

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('20090325')               --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('090325')                 --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('20092503')               --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('092503')                 --NO

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-MAR-2009')            --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 MAR 2009')            --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-MAR-09')              --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 MAR 09')              --YES

/* ======================================= */

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03-25-2009 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-03-2009 12:34:56')    --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009-03-25 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009-25-03 12:34:56')    --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03/25/2009 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25/03/2009 12:34:56')    --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009/03/25 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009/25/03 12:34:56')    --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('03 25 2009 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 03 2009 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009 03 25 12:34:56')    --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('2009 25 03 12:34:56')    --NO

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('20090325 12:34:56')      --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('090325 12:34:56')        --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('20092503 12:34:56')      --NO
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('092503 12:34:56')        --NO

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-MAR-2009 12:34:56')   --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 MAR 2009 12:34:56')   --YES

-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25-MAR-09 12:34:56')     --YES
-- INSERT INTO [dbo].[Scott_Date_Testing] ([EffectiveDate]) VALUES
('25 MAR 09 12:34:56')     --YES

25-Mar-09 11:30 PM

YYYYMMDD is guaranteed to be safe.  Can't your ETL software just strip out
the dashes?

YYYY-MM-DD is *pretty* safe.  However:

SET LANGUAGE FRANCAIS;
GO
SELECT CONVERT(DATETIME, '2009-06-13');

-- oops:

Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a
créé une valeur hors limites.


When you say all of those formats work, but YYYY-MM-DD doesn't I assume, can
you be more specific?





On 3/25/09 9:04 PM, in article
7918923f-ba91-402d-a974-b67249d458ab@n7g2000prc.googlegroups.com, "Scott

27-Mar-09 03:06 AM

On Mar 26, 2:30=A0pm, "Aaron Bertrand [SQL Server MVP]"
out

Yes I can do that.  But the other databases I work with accept YYYY-MM-
DD.  YYYY-DD-MM seems "non-standard" to me, although I admit I haven't
researched if there are standards regarding a date string
specification for an SQL-compliant database.



can

INSERT INTO [dbo].[Scott_DateTesting] ([Date]) VALUES ('2009-03-25') --
NO

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

26-Mar-09 12:58 AM

For SQL Server, the safe format is YYYYMMDD.  All other formats are subject
to different interpretation depending on language, dateformat, and regional
settings.

For any SQL-compliant database, I'm afraid you will have to research each
database platform independently.  I am sure each platform has its own set of
rules on how to interpret dates that are passed as string literals in
various formats.


???


Ok, so it sounds like the server where you are running this (or at least the
session where the statement is running) has non-standard language,
dateformat, or regional settings.  I cannot reproduce this on my
workstation, but of course I am running US English and I haven't altered my
dateformat or regional settings.  In this situation you would have no
problem using YYYY-MM-DD, but if you are using some other settings then all
bets are off.  Can't really suggest what to change in order to make the
above statement work, without having a better idea of how your server is set
up.  I do know that if you change the string literal to '20090325' then it
will work no matter how you have configured any of these settings.

A

26-Mar-09 04:10 AM

In addition to what Aaron has said in this thread:

One option is to make sure that the login who is executing your TSQL
code (import or whatever it is) has a language which interprets these
correctly. I.e., a login in SQL Server has a language attribute which
among other thing controls these things (for language dependent
datetime formats). See sp_helplanguage.

Another is to add SET DATEFORMAT ymd in the beginning of your TSQL
script.

Some reference text:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


On Mar 26, 2:30 pm, "Aaron Bertrand [SQL Server MVP]"

Yes I can do that.  But the other databases I work with accept
YYYY-MM-
DD.  YYYY-DD-MM seems "non-standard" to me, although I admit I haven't
researched if there are standards regarding a date string
specification for an SQL-compliant database.




INSERT INTO [dbo].[Scott_DateTesting] ([Date]) VALUES
('2009-03-25') --
NO

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated.

27-Mar-09 03:06 AM

Thanks all.  I will use YYYYMMDD format to avoid regional
interpretation.

BTW, I'm guessing our language is EN_Australian, but 1) I'm not sure,
and 2) it's moot given the above.

P.S.:  @Aaron:  <munch> meant I'd deleted lines from your post.  Sorry
for the confusion.

26-Mar-09 07:31 AM

No, it shouldn't be since I didn't find such language in the output
from sp_helplanguage. Anyhow, you can see the language as an attribute
for the SQL Server login you are using (using for instance
sp_helplogins).



Agreed. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

27-Mar-09 03:06 AM

Actually, that is the ONLY format allowed in Standard SQL.  This si
probably why your ETL tool prefers it.  Microsoft is the one who is
out of step .... again.

26-Mar-09 10:50 AM

...but not for long (at least not for the new breed of date related
types):
http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

26-Mar-09 11:02 AM

Imagine, a vendor making their product more flexible as a benefit to their
users, instead of bowing to the almighty standard.  How obnoxious.  That's
it!  I've had it!  I'm switching to a vendor that doesn't do ANYTHING
proprietary and ONLY adheres to the archaic standard, you know, like......
um, hmmm, I'm at a loss, never mind.




On 3/26/09 9:47 AM, in article
becf2741-6386-47f1-89ac-a16430f29bee@f41g2000pra.googlegroups.com,