Microsoft Year 2000 Readiness Disclosure & Resource Center

Product Summary

Product: SQL Server Version: 7.0 Category: Compliant Operating System: Win NT Language: English Release Date: 01 Nov 1998 Operational Range: 01 Jan 1753 - 31 Dec 9999 Prerequisites: None Product Dependencies: Distributed Transaction Coordinator, Microsoft Management Console, Microsoft Data Access Components, DCOM95, Microsoft Windows NT 4.0, Microsoft Windows 95, Microsoft Windows 98, Internet Explorer 4.0. Clock Dependencies: System clock Last Updated: 19 May 1999

Product Details

Product Maintenance: While Microsoft continues to recommend that customers install the most current Service Pack/Release for non-Year 2000 reasons, we understand that, for many reasons, this may not be possible. In order to aid our customersÆ Year 2000 efforts, Microsoft intends to maintain SQL Server 7.0 as compliant through January 1, 2001. Newer Service Packs are also to be maintained as compliant, and may include additional non-Year 2000 updates. This is intended to minimize the Year 2000 as a reason to upgrade. Includes Standard, Enterprise, Small Business, Evaluation Version How the product handles dates: 1) Server: SQL Server has two date/time data types: datetime and smalldatetime. The datetime data type is stored in 8 bytes of two 4-byte integers. The first 4 bytes are for the number of days before or after the base date of January 1, 1900, and the other 4 bytes are for the number of milliseconds after midnight. The values for datetime range from January 1, 1753, through December 31, 9999, to an accuracy of one three-hundredth of a second (3.33 milliseconds). Smalldatetime is less precise than datetime, and is stored in 4 bytes consisting of one small integer for the number of days after January 1, 1900, and one small integer for the number of minutes past midnight. The values for smalldatetime range from January 1, 1900, through June 6, 2079, with accuracy to the minute. Either data type allows users to specify only the last 2 digits of the year. However the year is still stored the same as if 4 digits were specified. The "two digit year cutoff" configuration option defines how SQL Server 7.0 expands a 2-digit year to four digits. The option is specified as a 4-digit year, the default is 2049. If only two digits are specified for a year, and they are less than or equal to the last two digits of the 2-digit year cutoff option, the date is stored as being in the same century as the option. If the specified two digits are greater than the last two digits of the 2-digit year cutoff option, the date is stored as being in the century before the one specified in the option. For example, if the 2-digit year cutoff option is left at its default of 2049, the value 49 is stored as 2049 and 50 is stored as 1950. If the 2-digit year cutoff option is set to 2029, the value 29 is stored as 2029 and 30 is stored as 1930. Earlier versions of SQL Server do not have a configurable 2-digit year cutoff option; they operate using a cutoff of 2049. 2) SQL Administration Tools: SQL Server administration tools include SQL Server Enterprise Manager, SQL Server Profiler, SQL Server Query Analyzer, SQL Server Service Manager, Client Network Utility, Server Network Utility, SQL Server Agent, and more than 25 wizards. Several of these tools allow display and/or input of date information. Date display: Uses either SQL Server Engine formatting or Microsoft Windows NT« Control Panel settings formatting. Date input: Dates are achieved using either Edit fields or Date controls. Transport means for date information to the server include stored procedures, SQL Server Distributed Management Framework objects, OLE DB Provider for SQL Server, SQL Server ODBC driver, DB-Library, and Net-Libraries. Plausibility validation of entered dates happens mostly at the Microsoft Data Engine level for datetime and smalldatetime data types. SQL Server Agent, the job and alert scheduling engine of SQL Server, represents an exception: date information for scheduled jobs is stored in integer data types. Plausibility of entered dates in this case is evaluated within the user interface Date controls and by using the SQL Server ISDATE() function within stored procedures prior to storage in the database. 3) SQL Data Management Objects Some SQL Data Management Objects have properties that represent dates. These properties are implemented as long integers in which dates are represented by numbers having the pattern YYYYMMDD. For example, the date April 19, 1998 is represented by the number 19980419. 4) Data Transformation Services Data Transformation Services (DTS) supports transformations that map source string columns (DBTYPE_STR or DBTYPE_WSTR) to destination date columns (DBTYPE_DATE). The date portion of the strings in the source columns must be in the OLE DB format of YYYY-MM-DD. Transformations can also map source date columns to destination string columns, in which case the date portion is converted to a string having the OLE DB format of YYYY-MM-DD. For example, the OLE DB format for April 19, 1998 is 1998-04-19. Transformations using scripts, such as Microsoft ActiveX« or Microsoft Visual Basic« scripts, can also use the CDate() function for inputting dates. How the applications and components shipped with SQL Server 7.0 handle dates:   Microsoft English Query:   Date storage Microsoft English Query does not store dates. SQL generation Date constants in generated SQL conditions use the generic date format YYYYMMDD. Year constants generated in SQL conditions use the format YYYY or YY, depending on a developer-controlled English Query setting on each year field. For fields marked to use the YY format, the century digits are simply omitted from the SQL condition. Formatting Dates in the testing tool are displayed using the ODBC text date format, which defaults to YYYY-MM-DD. ODBC can be configured to use regional settings instead. Parsing on date entry Dates can be input in questions using standard United States textual date formats. These include, but are not limited to: Slash-formatted dates: (M)M/(D)D/(YY)YY Text full dates: Month (D)D, (YY)YY Text months: Month (YY)YY Slash-formatted months: (M)M/(YY)YY Two digit shortcut handling Microsoft English Query interprets years entered with a 2-digit shortcut as: 1/1/00 through 12/31/29 is interpreted as 1/1/2000 through 12/31/2029. 1/1/30 through 12/31/99 is interpreted as 1/1/1930 through 12/31/1999. Microsoft OLAP Services:   Date storage Microsoft OLAP Services stores dates internally in the OLAP Services repository in accordance with the OLE DB for OLAP specification. Formatting The OLAP Services user interface does not include fields for date entries. Date information may be exposed through Decision Support Objects (DSO) and multidimensional expressions (MDX). These rely on the Year2000 compliance of the Microsoft Visual Basic« environment. Processing queries and aggregations Bounds checking and processing of queries and aggregations before, during, and after the rollover of January 1, 2000 have been verified and complete successfully.   Microsoft Repository:   Date storage Repository accepts dates in the range January 1, 100 to December 31, 9999. It does not truncate the year to a 2-digit year, so no reconversion is required at retrieval time. It stores the year in the form YYYY (in a DATE_STRUCT or TIMESTAMP_STRUCT structure, or the SQL Server datetime data type). Formatting Dates can be displayed in the format of MM/DD/YY, but this does not cause ambiguity because the dates are stored in SQL Server with 4-digit years. Two digit shortcut handling Applications must input dates using 4-digit years, attempting to input a 2-digit year results in an error. Engine-generated dates specify 4-digit years.   Microsoft Search Service: The Microsoft Search Service that ships with SQL Server 7.0 works with data in character columns and has no dependencies on date data. Testing guidelines and recommendations: Examine the database schema to see whether smalldatetime datatypes are used; in some cases, in order to achieve higher precision, it is recommended to convert these to the datetime data type. Use 4-digit year input to avoid ambiguities or boundary conditions in applications. Investigate front-end application behavior using SQL Server as a backend and interaction with other applications.

Return to Search Screen

If after reviewing this information you have additional questions related to this product, click here.

ANY MICROSOFT YEAR 2000 STATEMENTS MADE TO YOU IN THE COURSE OF PROVIDING YEAR 2000 RELATED UPDATES, YEAR 2000 DIAGNOSTIC TOOLS, OR REMEDIATION SERVICES (IF ANY) ARE SUBJECT TO THE YEAR 2000 INFORMATION AND READINESS DISCLOSURE ACT (112 STAT. 2386). IN CASE OF A DISPUTE, THIS ACT MAY REDUCE YOUR LEGAL RIGHTS REGARDING THE USE OF ANY SUCH STATEMENTS, UNLESS OTHERWISE SPECIFIED BY YOUR CONTRACT OR TARIFF.

This site is being designated as a Year 2000 Readiness Disclosure and the information contained herein is provided pursuant to the terms hereof and the Year 2000 Information and Readiness Disclosure Act.