T-SQL

I had run into this problem once after which I avoid 2 digit years. But now I know the reason for the issue. Thanks for the Question and the explanation.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Very good question! More of these please.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 5

We recommend specifying four-digit years.

It's best to use the format 'yyyymmdd' because it's indipendent from "regional setting".

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

I got this right, but I think the question should have been qualified with something like "on an instance installed with the default settings". Otherwise the answer should be "it depends on the value of the two digit year cutoff setting in sp_configure".

John

anders-731262 Ten Centuries Points: 1256 More actions · Answer 7

John Mitchell-245523 (12/15/2010)
I got this right, but I think the question should have been qualified with something like "on an instance installed with the default settings". Otherwise the answer should be "it depends on the value of the two digit year cutoff setting in sp_configure".
John

Since nothing was said about settings, we cant really assume anything other than "default". Of course, had there been a "depends" option, this should have been correct.

Good question.

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 8

Good Question, but not valid for all languages, since some have different date formats (ymd):

Japanese
Swedish
Hungarian
Croatian
Latvian
Lithuanian
Traditional Chinese
Korean
Simplified Chinese

SELECT dateformat, name, alias FROM sys.syslanguages WHERE dateformat NOT LIKE '%y'

Best Regards,

Chris Büttner

cengland0 SSCertifiable Points: 6102 More actions · Answer 9

John Mitchell-245523 (12/15/2010)
I got this right, but I think the question should have been qualified with something like "on an instance installed with the default settings". Otherwise the answer should be "it depends on the value of the two digit year cutoff setting in sp_configure".
John

You beat me to it. When I was reading the question, I was thinking it's not possible to made a determination of the output without knowing the settings. I had to use the default settings and hope for the best and did get it right.

Also, to check the cutoff, I normally use SQL Server Management Studio, connect to the server, right click on the server name in the Object Explorer, then select Properties. A window pops up with several pages listed in the left-hand navigation bar. Select Advanced and you can see it defaults to 2049 as the cutoff. You can even change it from that window using the range 1753 to 9999.

SQL Editor SSCommitted Points: 1980 More actions · Answer 10

SQL Editor

SSCommitted

Points: 1980

December 15, 2010 at 3:47 am

#1262725

Good....

ziangij SSCrazy Eights Points: 8066 More actions · Answer 11

ziangij

SSCrazy Eights

Points: 8066

December 15, 2010 at 4:13 am

#1262730

nice question, thanks 🙂

shaycullen Right there with Babe Points: 781 More actions · Answer 12

None of these are correct - as you are casting as "datetime" the results will be as follows;

2002-02-02 00:00:00.000

2049-02-02 00:00:00.000

1950-02-02 00:00:00.000

2000-02-02 00:00:00.000

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

shaycullen (12/15/2010)
None of these are correct - as you are casting as "datetime" the results will be as follows;
2002-02-02 00:00:00.000
2049-02-02 00:00:00.000
1950-02-02 00:00:00.000
2000-02-02 00:00:00.000

:rolleyes:

Yes, the time portions were very relevant for this question.

When you specify 2002-02-02 in SQL Server, 2002-02-02 00:00:00.000 is automatically assumed, so the answers are still correct.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 14

Good question. I wasn't positive of the cut off, so I had to do some research and learn something. Thanks.