Entering the Date

Dates consist of a month, day, and year and can be entered in a variety of formats for date, datetime, bigdatetime, bigtime and smalldatetime.

This table describes the acceptable formats for entering the date portion of a datetime or smalldatetime value:

Date Formats for Date and Time Datatypes

Date Format

Interpretation

Sample Entries

Meaning

4-digit string with no separators

Interpreted as yyyy. Date defaults to Jan 1 of the specified year.

“1947”

Jan 1 1947

6-digit string with no separators

Interpreted as yymmdd.

For yy < 50, year is 20yy.

For yy >= 50, year is 19yy.

“450128”

“520128”

Jan 28 2045

Jan 28 1952

8-digit string with no separators

Interpreted as yyyymmdd.

“20150415”

Apr 15 2015

String consisting of 2-digit month, day, and year separated by slashes, hyphens, or periods, or a combination of the above

The dateformat and language set options determine the expected order of date parts. For us_english, the default order is mdy.

For yy < 50, year is interpreted as 20yy. For yy >= 50, year is interpreted as 19yy.

“12/15/94”

“12.15.94”

“12-15-94”

“12.15/94”

All of these entries are interpreted as Dec 15 1994 when the dateformat option is set to mdy.

String consisting of 2-digit month, 2-digit day, and 4-digit year separated by slashes, hyphens, or periods, or a combination of the above

The dateformat and language set options determine the expected order of date parts. For us_english, the default order is mdy.

“04/15.1994”

Interpreted as Apr 15 1994 when the dateformat option is set to mdy.

Month is entered in character form (either full month name or its standard abbreviation), followed by an optional comma

If 4-digit year is entered, date parts can be entered in any order.

“April 15, 1994”

“1994 15 apr”

“1994 April 15”

“15 APR 1994”

All of these entries are interpreted as Apr 15 1994.

If day is omitted, all 4 digits of year must be specified. Day defaults to the first day of the month.

“apr 1994”

Apr 1 1994

If year is only 2 digits (yy), it is expected to appear after the day.

For yy < 50, year is interpreted as 20yy.

For yy >= 50, year is interpreted as 19yy.

“mar 16 17”

“apr 15 94”

Mar 16 2017

Apr 15 1994

The empty string “”

Date defaults to Jan 1 1900.

“”

Jan 1 1900