Here is what books online
says about the acceptable aphabetic and numeric formats that are automatically
recognized as dates by SQL Server:
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.
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] [,] 1996
- Apr[il] 15[,] 96
- Apr[il] 1996 
-  Apr[il][,] 1996
- 15 Apr[il][,]96
- 15 96 apr[il]
-  1996 apr[il]
- 1996 APR[IL] 
- 1996  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.
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]
numeric formats are valid:
4/15/96 -- (mdy)
4-15-96 -- (mdy)
4.15.96 -- (mdy)
/96/15 -- (myd)
15/4/96 -- (dmy)
15/96/4 -- (dym)
96/15/4 -- (ydm)
96//15 -- (ymd)
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.
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.