This is actually a spin-off from a Question of the Day, which appeared in SQLServerCentral (December 16, 2010).

A brief History

For those of you who have been around in the industry for more than a decade, the term Y2K carries mixed feelings. It was a very much hyped and very real problem, but with a very simple solution. A lot of our software systems were found to be vulnerable, and in hindsight, it was because of the human tendency to assume. Most of the systems designers did not predict that their systems would run for 20 or so years, and were hence bitten by the famous “Y2K bug”.

For those of came in late, the Y2K issue was valid only for systems using a 2 digit year representation. Before the year 2000, most of us had a habit of using 98 to represent the year 1998 and so on. Most systems were designed keeping this in mind, and hence when the year 2000 would set in, the year 00 would thus become, 1900 – throwing all financial calculations out of the window.

The “Y2K” Solution

There were two possible solutions:

1. Use a 4 digit year
2. Change the assumptions:
   • Anything greater than “50” would mean we are in the 20th century (i.e. 50 is 1950, 60 is 1960 and so on)
   • Anything less than “50” is the 21st century (i.e. 00 is 2000, 10 is 2010 and so on)

Those who used 4 digit years (option #1) are covered, and will continue to remain covered till we roll over to 5 digit years, and if the systems we build today are still running. However, for those using option #2, the ghost of the Y2K is not yet gone. In the year 2049, the ghost will come back to haunt us.

Most systems, especially today’s database systems have been designed keeping both options in mind. Microsoft SQL Server also accepts two digit years and converts them into a 4 digit year value based on the logic above. The default two-digit year cutoff for Microsoft SQL Server is the year 2049.

Depending upon where and how you do business, the business requirement behind the two-digit year cutoff may vary. Microsoft SQL Server does provide a way to override this default value of 2049 via an advanced configuration option. Today, I will be demonstrating how to use the same.

A Demonstration

Consider the following example:

By default, Microsoft SQL Server has it’s two-digit year cutoff as 2049, which causes the two digit year “50” to be interpreted as “1950”.

Just as where there is a will, there is always a way; similarly, where there are ghosts, there are always ghost busters. The ghost buster in this case has been provided by Microsoft in the form of a configurable 2-digit year cutoff value.

The steps below show us how to change the default two digit year cutoff:

1. The two digit year cutoff is an advanced option. Hence, turn advanced options ON
2. Check the default/current value of the two digit year cutoff
3. Change the value of the two digit year cutoff
4. Check that the modifications have taken effect

NOTE: I have used the keyword “RECONFIGURE”, which specifies that the configuration setting does not require a server stop and restart, and that the currently running value should be updated. Please use this option with utmost care.

Now, let us run the example again:

Notice that the year “49” is now interpreted as the year “1949”. We have successfully changed the two digit cutoff year.

Now, let’s rollback the settings of the SQL server instance to leave it as we found it.

In Conclusion

As always, nothing comes without it’s own pros and cons. Before you go about changing (or not changing) the two digit cutoff value of your SQL Server, please keep the following in mind:

1. Per MSDN (http://msdn.microsoft.com/en-us/library/ms189577.aspx), the two digit year cutoff values for SQL Server and OLE Automation objects are different. That of SQL Server is 2049, while that of OLE Automation objects is 2030. If your client applications use 2-digit years, it is advisable to have both in sync
2. The two-digit year provision should be treated as limited-time feature. Applications should be developed using, or should plan to move to 4 digit years

The above demonstration was just for changing the behaviour of Microsoft SQL Server. Other applications (like Excel) are also affected by the “ghost of the Y2K” and separate efforts and planning should be done to avoid any issues. For Excel users, please ensure that you have the required range set in Control Panel –> Regional and Language Options –> Customize –> Date tab.

I trust that the above gives you enough of a heads-up about the upcoming ghost of Y2K and a possibly increased time window to re-engineer your system. If you need the scripts that I ran through in this post, please visit my profile on this site.

Have a good week-end!

Be courteous. Drive responsibly.