Microsoft KB Archive/268593

= PRB: Querying a DATETIME Value Without Quotes Gives Unexpected Results =

Article ID: 268593

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q268593



SYMPTOMS
Referring to datetime values without enclosing them in quotes does not cause an error. However, the results are not the same as if the value were enclosed in quotes.



CAUSE
According to SQL Server 7.0 Books Online, in the &quot;Using Date and Time Data&quot; topic, math functions can be directly used on a datetime field.

SQL Server recognizes date and time data enclosed in single quotation marks (‘) in these formats:


 * Alphabetic date formats (for example, ‘April 15, 1998’).


 * Numeric date formats (for example, ‘4/15/1998’, ‘April 15, 1998’).


 * String formats that are not separated (for example, ‘19981207’, ‘December 12, 1998’).

Therefore, a value without quotes that is used as a datetime value is treated as the default date plus the number of days equal to the computed amount of the assigned value. Numbers are rounded before they are added to the default date when necessary. For example, 12-1-2005 equates to the default date plus (12 minus 1 minus 2005) days. That equals January 1, 1900 plus (-1994 days). The final result is &quot;1894-07-17 00:00:00.000&quot;.



RESOLUTION
If you follow the information in SQL Server Books Online you will not experience the problem described in this article.



MORE INFORMATION
Here is an example that compares the results from various date formats: SET NOCOUNT ON CREATE TABLE DatetimeMath (DTColumn  DATETIME    NOT NULL, Results        VARCHAR(60) NOT NULL) GO INSERT DatetimeMath VALUES ('12/1/2005', 'Exact Date') INSERT DatetimeMath VALUES (' ', 'Default Date') INSERT DatetimeMath VALUES (12-1-05, 'Default Date PLUS (12 MINUS 1 MINUS 05) days') INSERT DatetimeMath VALUES (12-1-2005, 'Default Date PLUS (12 MINUS 1 MINUS 2005) days') INSERT DatetimeMath VALUES (12/1/2005, 'Default Date PLUS (12 DIVIDED BY 1 DIVIDED BY 2005) days') INSERT DatetimeMath VALUES (12/3, 'Default Date PLUS (12 DIVIDED BY 3) days') GO SELECT Results, DTColumn FROM DatetimeMath GO DROP TABLE DatetimeMath SET NOCOUNT OFF Results                                                  DTColumn - --- Exact Date                                               2005-12-01 00:00:00.000 Default Date                                             1900-01-01 00:00:00.000 Default Date PLUS (12 MINUS 1 MINUS 05) days             1900-01-07 00:00:00.000 Default Date PLUS (12 MINUS 1 MINUS 2005) days           1894-07-17 00:00:00.000 Default Date PLUS (12 DIVIDED BY 1 DIVIDED BY 2005) days 1900-01-01 00:00:00.000 Default Date PLUS (12 DIVIDED BY 3) days                 1900-01-05 00:00:00.000

Keywords: kbprb KB268593

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.