THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #1 Dateformat

Just to clarify, summer starts Memorial Day and ends Labor Day. I will post a teaser every Friday until Labor Day.

Got this from my friend George, try to guess the output of the code below without running it.

 

SET DATEFORMAT DMY

SELECT CONVERT(DATETIME, '120607')

 

Published Friday, June 01, 2007 5:32 AM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

snewfie said:

June 7th 2012 ?

Very weird

June 1, 2007 7:09 AM
 

Denis Gobo said:

Not weird at all, think about it for a second

June 1, 2007 7:42 AM
 

Olivier said:

yes, very weird !!!

can you explain us ?

June 1, 2007 8:58 AM
 

Denis Gobo said:

When there are only six digits, SQL Server will use the cutoff Date for century (50 and above is 1950, 49 and below is 2049).

The SET DATEFORMAT was added to add confusion  ;-)

SET DATEFORMAT

This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.

June 1, 2007 9:10 AM
 

Scott R. said:

SET DATEFORMAT did not apply in the example given because there were no "date part" delimiters used within the date string (like / or -) to separate months from days from years.  It appears that date strings lacking such delimiters will always convert as an impled year/month/day format, with 2-digit years being used if the undelimited date string is 6 digits and 4-digit years if the undelimited date string is 8 digits.  7-digit undelimited dates don't work, since the oldest date allowed is 01/01/1753 for the datetime data type and 01/01/1900 for the smalldatetime data type.  5-digit or less and 9-digit or more undelimited date strings also don't seem to work, since the implicit string to date conversion function appears to require exactly 6 or 8 digits.  Trailing spaces appear to be OK - just no extra digits.

Try this extended example:

SET DATEFORMAT DMY

SELECT 'SET DATEFORMAT DMY'

SELECT CONVERT(DATETIME, '120607'), '120607'

SELECT CONVERT(DATETIME, '010203'), '010203'

SELECT CONVERT(DATETIME, '17530101'), '17530101'

SELECT CONVERT(DATETIME, '17521231'), '17521231'

SELECT CONVERT(DATETIME, '19120607'), '19120607'

SELECT CONVERT(DATETIME, '20120607'), '20120607'

SELECT CONVERT(DATETIME, '20020203 '), '''20020203 '''

SELECT CONVERT(DATETIME, '12/06/07'), '12/06/07'

SELECT CONVERT(DATETIME, '12/06/2007'), '12/06/2007'

SELECT CONVERT(DATETIME, '06/07/1912'), '06/07/1912'

SELECT CONVERT(DATETIME, '06-07-1912'), '06-07-1912'

GO

SELECT CONVERT(DATETIME, '20607'), '20607'

GO

SELECT CONVERT(DATETIME, '0020607'), '0020607'

GO

SET DATEFORMAT MDY

SELECT 'SET DATEFORMAT MDY'

SELECT CONVERT(DATETIME, '120607'), '120607'

SELECT CONVERT(DATETIME, '010203'), '010203'

SELECT CONVERT(DATETIME, '17530101'), '17530101'

SELECT CONVERT(DATETIME, '17521231'), '17521231'

SELECT CONVERT(DATETIME, '19120607'), '19120607'

SELECT CONVERT(DATETIME, '20120607'), '20120607'

SELECT CONVERT(DATETIME, '20020203 '), '''20020203 '''

SELECT CONVERT(DATETIME, '12/06/07'), '12/06/07'

SELECT CONVERT(DATETIME, '12/06/2007'), '12/06/2007'

SELECT CONVERT(DATETIME, '06/07/1912'), '06/07/1912'

SELECT CONVERT(DATETIME, '06-07-1912'), '06-07-1912'

GO

Good SQL teaser - keep them coming!

June 1, 2007 10:26 AM
 

Olivier said:

I'm waiting next friday for another great teaser !!

June 1, 2007 3:47 PM
 

Thava said:

it good

July 30, 2007 3:16 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement