Entering date and time data

The datetime, smalldatetime, bigdatetime and bigtime datatypes consist of a date portion either followed by or preceded by a time portion. (You can omit either the date or the time, or both.) The date datatype has only a date and the time datatype has only the time. You must enclose values in single or double quotes.

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:

Table 1-14 describes the acceptable formats for entering the date portion of a datetime or smalldatetime value:

Table 1-14: 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.

“19940415”

Apr 15 1994

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.

“4/15/94”

“4.15.94”

“4-15-94”

“04.15/94”

All of these entries are interpreted as Apr 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

Entering the time

The time component of a datetime, smalldatetime, or time value must be specified as follows:

hours[:minutes[:seconds[:milliseconds]] [AM | PM]

The time component of a bigdatetime or bigtime value must be specified as follows:

hours[:minutes[:seconds[.microseconds]] [AM | PM]

Displaying formats for datetime, smalldatetime, and date values

The display format for datetime and smalldatetime values is “Mon dd yyyy hh:mmAM” (or “PM”); for example, “Apr 15 1988 10:23PM”. To display seconds and milliseconds, and to obtain additional date styles and date-part orders, use the convert function to convert the data to a character string. Adaptive Server may round or truncate millisecond values.

Table 1-15 lists some examples of datetime entries and their display values:

Table 1-15: Examples of datetime and date entries

Entry

Value displayed

“1947”

Jan 1 1947 12:00AM

“450128 12:30:1PM”

Jan 28 2045 12:30PM

“12:30.1PM 450128”

Jan 28 2045 12:30PM

“14:30.22”

Jan 1 1900 2:30PM

“4am”

Jan 1 1900 4:00AM

Examples of date

“1947”

Jan 1 1947

“450128”

Jan 28 2045

“520317”

Mar 17 1952

Display formats for bigdatetime and bigtime

For bigdatetime and bigtime the value displays reflects a microsecond value. bigdatetime and bigtime have default display formats that accomodate their increased precision.

The format for time must be specified as:

Use 12 AM for midnight and 12 PM for noon. A bigtime value must contain either a colon or an AM or PM signifier. AM or PM can be entered in uppercase, lowercase, or mixed case.

The seconds specification can include either a decimal portion preceded by a point or a number of milliseconds preceded by a colon. For example, “12:30:20:1” means twenty seconds and one millisecond past 12:30; “12:30:20.1” means twenty and one-tenth of a second past.

To store a bigdatetime or bigtime time value that includes microseconds, specify a string literal using a point. “00:00:00.1” means one tenth of a second past midnight and “00:00:00.000001” means one millionth of a second past midnight. Any value after the colon specifying fractional seconds will continue to refer to a number of milliseconds. Such as “00:00:00:5” means 5 milliseconds.

Displaying formats for time value

The display format for time values is “hh:mm:ss:mmmAM” (or “PM”); for example, “10:23:40:022PM.

Table 1-16: Examples of time entries

Entry

Value displayed

"12:12:00”

12:12PM

“01:23PM” or “01:23:1PM”

1:23PM

“02:24:00:001”

2:24AM

Finding values that match a pattern

Use the like keyword to look for dates that match a particular pattern. If you use the equality operator (=) to search date or time values for a particular month, day, and year, Adaptive Server returns only those values for which the time is precisely 12:00:00:000AM.

For example, if you insert the value “9:20” into a column named arrival_time, Adaptive Server converts the entry into “Jan 1 1900 9:20AM.” If you look for this entry using the equality operator, it is not found:

where arrival_time = "9:20" /* does not match */

You can find the entry using the like operator:

where arrival_time like "%9:20%"

When using like, Adaptive Server first converts the dates to datetime or date format and then to varchar. The display format consists of the 3-character month in the current language, 2 characters for the day, 4 characters for the year, the time in hours and minutes, and “AM” or “PM.”

When searching with like, you cannot use the wide variety of input formats that are available for entering the date portion of datetime, smalldatetime, bigdatetime, bigtime, date, and time values. You cannot search for seconds or milliseconds with like and match a pattern, unless you are also using style 9 or 109 and the convert function.

If you are using like, and the day of the month is a number between 1 and 9, insert 2 spaces between the month and the day to match the varchar conversion of the datetime value. Similarly, if the hour is less than 10, the conversion places 2 spaces between the year and the hour. The following clause with 1 space between “May” and “2”) finds all dates from May 20 through May 29, but not May 2:

like "May 2%"

You do not need to insert the extra space with other date comparisons, only with like, since the datetime values are converted to varchar only for the like comparison.

Manipulating dates

You can do some arithmetic calculations on date and time datatypes values with the built-in date functions. See Transact-SQL Users Guide.