Microsoft® SQL Server™ allows you to specify date data with a numeric month specified. For example, 5/20/97 is the year 1997, the twentieth day of May. When using numeric date format, specify the month, day, and year in a string with slashes (/), 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.
| Date and Time Data | SET DATEFORMAT |
| datetime and smalldatetime |