0

I have run into a SQL snippet including date conversion with 6 digits in the format of YYMMDD.

SELECT CONVERT(DATETIME, '6011' + '01') AS testingdate

Why doesn't the followed query yield 2060-11-01 while '2011' + '01' yields 2020-11-01?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Soner from The Ottoman Empire
  • 18,731
  • 3
  • 79
  • 101
  • 2
    `2 digit year cut-off` please refer to https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option?view=sql-server-ver15 – Squirrel Oct 06 '20 at 07:07
  • 6
    `Why doesn't the followed query yield 2060-11-01` why should it? Most of the authors we developers read were born around 1960 and even earlier. The *real* question is why are two-digit dates still used in 2020 ? The system has a major bug that should have been fixed 20 years ago – Panagiotis Kanavos Oct 06 '20 at 07:09
  • 1
    And if you think that's an exaggeration, before COVID struck the big news in IT was that Lloyds, UBS and other big British banks had crashed because they covered up their `Y2K` problem by using `20` as a cutoff date. So when `2020-01-01` came, their systems thought it's 1920 and simply crashed – Panagiotis Kanavos Oct 06 '20 at 07:13
  • 1
    A reminder [Y2K? How about Y2.02K as Lloyds suffers its second ***** of the year](https://www.theregister.com/2020/01/02/lloyds_outage/). Fix the bug – Panagiotis Kanavos Oct 06 '20 at 07:16

1 Answers1

0

As @Squirrel commented, behavior for interpreting ambiguous 2-digit years is controlled by the 2 digit year cut-off configuration value. The value specifies an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, with a default configuration value of 2049.

The code below shows the behavior with default and custom 2 digit year cut-off values.

EXEC sp_configure 'show',1;
RECONFIGURE;

--default behavior: 2-digit years <= 49 use 20 as the century and > 49 use 19
EXEC sp_configure 'two digit year cutoff',2049;
RECONFIGURE;
GO
SELECT CAST('01-01-49' AS date); --2049-01-01
SELECT CAST('01-01-50' AS date); --1950-01-01
GO

----custom behavior: 2-digit years <= 49 use 19 as the century and > 49 use 18
EXEC sp_configure 'two digit year cutoff',1949;
RECONFIGURE;
GO
SELECT CAST('01-01-49' AS date); --1949-01-01
SELECT CAST('01-01-50' AS date); --1850-01-01
GO

----custom behavior: 2-digit years <= 49 use 21 as the century and > 49 use 20
EXEC sp_configure 'two digit year cutoff',2149;
RECONFIGURE;
GO
SELECT CAST('01-01-49' AS date); --2149-01-01
SELECT CAST('01-01-50' AS date); --2050-01-01
GO

--revert to recommended default value
EXEC sp_configure 'two digit year cutoff',2049;
RECONFIGURE;
GO

2-digit year cut-off is a kludge work-around for short-sighted programming practices of the past, IMHO. I suggest one follow the recommendation from the referenced documentation page so that ancestors don't inherit a Y2.1K problem:

To avoid ambiguity with dates, always use four-digit years in your data.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71