Changing the SQL Server Century Cut-Off Date
The work undertaken to resolve the year 2000 problem taught developers that they should be using four-digit dates. However, as integration with legacy systems is still required, so are two-digit dates. SQL Server provides a century cut-off point to help.
What is the Cut-Off Date?
People like to work with two-digit years. This means that user input or data from integrated legacy systems often provides datetime information with a two-digit year. For example, 89 for 1989 or 07 for 2007. For recent historical and future dates this is easily understood. However, when dates become more distant, some ambiguity is possible. For example, does 57 represent 1957 or 2057?
To allow dates to be automatically expanded from two digits to the four they will be processed and stored as, SQL Server implements a cut-off year. By default, in SQL Server 2005, this is 2049, meaning values 00 - 49 represent 2000 to 2049 and 50 - 99 are the years 1950 to 1999. The switchover point can be modified for a server if the range is unsuitable for the required application.
Viewing the Cut-Off Date
The current value of the cut-off date can be determined by executing the sp_configure stored procedure. The cut-off date is an advanced option in SQL Server 2005 so advanced options must be switched on using the stored procedure first. To turn on advanced options, execute the following statements:
sp_configure 'show advanced options', 1
You can now view the current cut-off year by executing the following statement, which shows the available range and the currently configured and running values.
sp_configure 'two digit year cutoff'
NB: You will see a configured value and the current 'run' value. The run value is set to the configured value with the reconfigure command.
Changing the Cut-Off Date
Changing the configured cut-off date uses the same sp_configure statement but with an additional parameter for the new value. This must be within the range shown by the command executed above. Executing sp_configure changes the configured year but not the running value so you also need to execute a 'reconfigure' command to use the new cut-off.
sp_configure 'two digit year cutoff', 2069
SELECT convert(DATETIME, '31 Dec 69') -- Year 2069
SELECT convert(DATETIME, '01 Jan 70') -- Year 1970
8 November 2007