re: Summer SQL Teaser #1 Dateformat

snewfie — Fri, 01 Jun 2007 11:09:35 GMT

June 7th 2012 ?

Very weird

re: Summer SQL Teaser #1 Dateformat

Denis Gobo — Fri, 01 Jun 2007 11:42:39 GMT

Not weird at all, think about it for a second

re: Summer SQL Teaser #1 Dateformat

Olivier — Fri, 01 Jun 2007 12:58:28 GMT

yes, very weird !!!

can you explain us ?

re: Summer SQL Teaser #1 Dateformat

Denis Gobo — Fri, 01 Jun 2007 13:10:05 GMT

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.

re: Summer SQL Teaser #1 Dateformat

Scott R. — Fri, 01 Jun 2007 14:26:37 GMT

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'

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

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

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'

Good SQL teaser - keep them coming!

re: Summer SQL Teaser #1 Dateformat

Olivier — Fri, 01 Jun 2007 19:47:16 GMT

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

re: Summer SQL Teaser #1 Dateformat

Thava — Mon, 30 Jul 2007 19:16:50 GMT

it good