INF: Trace Flag 8816 to Help Year 2000 Conversion (198416)

MORE INFORMATION

Microsoft SQL Server version 7.0 uses the new Two-Digit Year Cutoff configuration option to determine which century a two-digit date belongs in. This value minus 99 years determines the year boundaries for how a two-digit year is interpreted during conversion.

Example 1

sp_configure 'two digit year cutoff', 2070
go
reconfigure
go

This forces SQL Server 7.0 to interpret any two-digit year as occurring between 1971 and 2070. This means that 12/30/70 is interpreted as 12/30/2070 and 1/3/71 is interpreted as 1/3/1971.

This two-digit year cutoff value can be any year that occurs between 1753 and 9999; it can also be set using the Enterprise Manager interface. Right-click the SQL Server name and then click Properties. Set this value at the bottom of the Server Settings tab.

The trace flag 8816 can also be turned on to log all two-digit year to four-digit year conversions.

Example 2

DBCC TRACEON(8816)
go
SET DATEFORMAT mdy
go

CREATE TABLE BirthDay
go
INSERT BirthDay(Name, BirthDate)
VALUES('Chris Preston', '7/30/47')


Example 1 and Example 2 will print the following line in the error log:

SQL Server is behaving correctly. This is an application error. To avoid the problem you can either enter the full four-digit year (1947), or decrease the two-digit year cutoff value. Microsoft recommends that all programs use the full four-digit year for all date operations.