PRB: Querying a DATETIME Value Without Quotes Gives Unexpected Results (268593)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
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 "Using Date and Time Data" 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 "1894-07-17 00:00:00.000".

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
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbprb KB268593
©2003 Microsoft Corporation. All rights reserved.