Wiki Home

SQL - Date Types


Namespace: SQL
Microsoft® SQL Server™ provides two datetime data types: datetime (8 bytes) and smalldatetime (4 bytes). Unlike FoxPro, there is no date-only data type but you can provide only the date to either data type and the time portion will default to midnight (00:00:00). Unlike FoxPro, SQL Server datetime limits the date value to no earlier than January 1, 1753 through December 31, 9999. Smalldatetime offers a narrower range of January 1, 1900 through June 6, 2079.
Where empty time has a default value, an empty date defaults to January 1, 1900. This is a very irksome behavior compared to the blank date FoxPro allows. I opt to send NULLs to prevent obtaining a date value that may be valid within the range my users are entering.
Microsoft® SQL Server™ recognizes date and time data as strings enclosed by single quotation marks (') in these formats:
  1. Alphabetic date formats (for example, ‘April 15, 1998’)
  2. Numeric date formats (for example, ‘4/15/1998’, ‘April 15, 1998’)
  3. Unseparated string formats (for example, ‘19981207’, ‘December 12, 1998’)

10/23/2002 - Steven Blake The reverse operation to the DTOS() function has always seemed a curious omission in the supplied arsenal from Microsoft, especially before the introduction of the Strict Date Format. My remedy was a UDF (User Defined Function) called STOD(). Consideration for SQL Server Date ranges caused a moderation in STOD() and is described in String To Date.
It is very important to enclose the date value in quotes. For example, using 1/1/2002 (instead of '2002-1-1') will not cause an error but it will give incorrect results. SQL Server will interpret 1/1/2002 as a numerical division (1 divided by 1 divided by 2002) and will return a number. -- Hector Correa
Category SQL
( Topic last updated: 2002.10.23 09:14:16 PM )