INF: Trace Flag 8816 to Help Year 2000 Conversion

ID: Q198416

The information in this article applies to:

Microsoft SQL Server version 7.0

SUMMARY

The trace flag 8816 may help application designers and Year 2000 consultants find Year 2000 bugs in older programs. This trace flag logs every two-digit year conversion to a four-digit year.

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

(Name nvarchar(50),
BirthDate datetime)

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:

1998-12-22 18:36:51.23 spid7 2-digit year 47 converted to 2047.

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.

Additional query words: prodsql Y2K 2000 Year2000


Keywords          : 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: January 15, 1999