Have you ever encountered issues when working with two-digit years in your SQL queries? If so, you’re not alone. In this blog post, we will discuss the concept of the two-digit year cutoff in SQL Server and how it can impact your queries.

The two-digit year cutoff refers to the year after which a two-digit year is considered to be in the 20th century instead of the 21st century. For example, if the cutoff is set to 50, any two-digit year greater than or equal to 50 is considered to be in the 20th century, while any two-digit year less than 50 is considered to be in the 21st century.

This concept can be particularly important when dealing with dates and performing calculations or comparisons. If the two-digit year cutoff is not set correctly, it can lead to unexpected results and errors in your queries.

To check the value of the two-digit year cutoff in SQL Server, you can use the following command:

sp_configure 'two digit year cutoff'

If you are using a two-digit year in your queries, it is recommended to check the value of the two-digit year cutoff and ensure it is set correctly. To change the value, you can use the following commands:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'two digit year cutoff', [new value]

It is important to note that the ‘two digit year cutoff’ setting is considered an advanced option in SQL Server. Therefore, you need to enable advanced options before you can modify this setting.

By being aware of the two-digit year cutoff and ensuring it is set correctly, you can avoid potential issues and ensure accurate results in your SQL queries.

We would like to thank Andy Warren for bringing this topic to our attention and inspiring this blog post. If you’re interested in learning more about SQL Server, we highly recommend following Andy’s blog and checking out SQLShare.com.

For more SQL Server tips and insights, be sure to follow us on Twitter at @briankmcdonald and subscribe to our RSS feed.

Thank you for reading!