I couldn't sleep this morning and decided to wake up and catch up on my email when I noticed a video link from SQL Share regarding converting and the two digit year cutoff. It has been a long time since I had heard about this potentially critical issue when using two digit years a query. In his video, Andy Warren (BLOG | TWITTER) gave a good idea to check for the value of this using sp_configure 'two digit year cutoff' if you are using a two digit year in your queries. Now I particularly wouldn't use two digit years, but some may and as such his recommendation is a good one. One thing to note though, the 'two digit year cutoff' setting is under the advanced settings. So be sure to set it to advanced options first. Otherwise, you may get this message:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'two digit year cutoff' does not exist, or it may be an advanced option.

Use this to turn on run the below statement to turn advanced options on and then reconfigure the change. Then check or reset the two digit year cutoff value as Andy stated in his video.

exec sp_configure 'show advanced options', 1

reconfigure

sp_configure 'two digit year cutoff' 

I would like to thank Andy for posting this as an inspiration for this blog and as reminder to check for the two digit year value. If you don’t know Andy or anything about SQL Share, I think that you’ll enjoy his words of wisdom. I encourage you to follow his blog, hit tweets and at SQLShare.com.

Please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me at any of the below methods.

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald