Export (0) Print
Expand All
1 out of 10 rated this helpful - Rate this topic

Numeric Date Format

SQL Server 2005

In Microsoft SQL Server 2005 you can specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May 1997. When you use 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]

The following numeric formats are valid:

[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[0]4/[19]96/15 -- (myd)

15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[0]4/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.

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.