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
SQL Server 2005
(1)
Varchar
(1)
Date
(1)
DtFormatsInput
(1)
Database
(1)
EffectiveDate
(1)
  Aaron Bertrand [SQL Server MVP] replied...
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
  Scott Bass replied...
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.
  Aaron Bertrand [SQL Server MVP] replied...
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
  Tibor Karaszi replied...
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.
  Scott Bass replied...
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.
  Tibor Karaszi replied...
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
  --CELKO-- replied...
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.
  Tibor Karaszi replied...
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
  Aaron Bertrand [SQL Server MVP] replied...
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,
Create New Account
help
SQL Server Cannot restart SQL Server service I recently installed SQL Server 2008 Express Edition with Advanced Tools. This is the version information from SQL Server Management Studio Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709- 1414 ) Microsoft
SQL Server MSDE on Windows 2003 R2 box, new DL385G6 - Install Fails during SQL Services I have been finding that I am having trouble with the Crystal Reports Server XI installation failing when it is dealing with SQL. So, as a thought and in case there was something wrong with my SQL portion of the isntall. I thought ok, I will try installing the actual MSDE application direct from Microsoft. So, I downloaded the MSDE for SQL 2000 (which is msde2000a.exe), set my switches and off to the races. It quit seconds left to the installation and bombed with the same errors as the Crystal Reports Server install. The error is the same whether I try to install MSDE by itself or
SQL Server unable to connect to sql server remotely This is a multi-part message in MIME format. - -- -- - = _NextPart_000_0006_01C831AE.4FC2B4D0 Content-Type: text charset = "iso-8859-1" Content-Transfer-Encoding: quoted-printable Here is my issue. I installed SQL 2005 on one of my servers. Up until 10 = minutes ago I was able to connect to the server remotely using SQL = Server Management Studio but I was unable connect to the SQL server from = the actual
SQL Server Why_can’t_recursive_queries_contain. . .? hi I hope I didn = 92t put too many questions 1) Why can such as this is already a strong case to simply disallow the option. - - Hugo Kornelis, SQL Server MVP My SQL Server blog: http: / / sqlblog.com / blogs / hugo_kornelis MS SQL Server is a minimal subset of the ANSI / ISO Standards. In Standard SQL, the WITH clause