Working with SQL Server Date/Time Variables - Page 2

Here is what books online says about the acceptable aphabetic and numeric formats that are automatically recognized as dates by SQL Server:

Alphabetic Date Format

Microsoft. SQL Server™ 2000 allows you to specify date data with a month specified as the full month name (for example, April) or the month abbreviation (for example, Apr) given in the current language; commas are optional and capitalization (case) is ignored.

Here are some guidelines for the use of alphabetic date formats:

• Enclose the date and time data in single quotation marks (').
• These are the valid alphabetic formats for SQL Server date data (characters enclosed in brackets are optional):
• Apr[il] [15][,] 1996
• Apr[il] 15[,] [19]96
• Apr[il] 1996 [15]
• [15] Apr[il][,] 1996
• 15 Apr[il][,][19]96
• 15 [19]96 apr[il]
• [15] 1996 apr[il]
• 1996 APR[IL] [15]
• 1996 [15] APR[IL]
• If you specify only the last two digits of the year, values less than the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years.
• If the day is missing, the first day of the month is supplied.
• The SET DATEFORMAT session setting is not applied when you specify the month in alphabetic form.

Numeric Date Format

Microsoft® SQL Server™ 2000 allows you to specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May, 1997. When using numeric date format, specify the month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form:

number separator number separator number [time] [time]

These numeric formats are valid:

[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[04]/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[04]/15 -- (ymd)

When the language is set to us_english, the default order for the date is mdy. You can change the date order with the SET DATEFORMAT statement, which can also affect the date order, depending on the language.

The setting for SET DATEFORMAT determines how date values are interpreted. If the order does not match the setting, the values are not interpreted as dates (because they are out of range), or the values are misinterpreted. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting.