SQL Server Brain Basher of the Week #003

This week Brain Basher is based on data/time. See below code and try to guess the output of it.

DECLARE @datetime datetime
SET @datetime = '01/01/00'
SELECT YEAR (@datetime)

SET @datetime = '01/01/30'
SELECT YEAR (@datetime)

SET @datetime = '01/01/40'
SELECT YEAR (@datetime)

SET @datetime = '01/01/50'
SELECT YEAR (@datetime)

What did you guess? Years of 2000, 2030, 2040, and 2050? If so, it is wrong. Here is the output;

As you see, the last one is not 2050, it is 1950. This is based on a setting called two digit year cutoff. The setting represents the cutoff year for interpreting two-digits year as four-digits year and default value for this is 2049. This sets the default time span as 1950-2049, hence value of 49 represents, 2049 and value of 50 represents 1950. Another example is, value of 99 represents 1999 and value of 01 represents 2001.

It is not advisable to change this but this setting can be changed using sp_configure.

For more info: https://msdn.microsoft.com/en-us/library/ms191004.aspx