datetime (Transact-SQL)

  • Article

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Note

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

datetime Description

Property

Value

Syntax

datetime

Usage

DECLARE @MyDatetime datetime

CREATE TABLE Table1 ( Column1 datetime )

Default string literal formats

(used for down-level client)

Not applicable

Date range

January 1, 1753, through December 31, 9999

Time range

00:00:00 through 23:59:59.997

Time zone offset range

None

Element ranges

YYYY is four digits from 1753 through 9999 that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

Character length

19 positions minimum to 23 maximum

Storage size

8 bytes

Accuracy

Rounded to increments of .000, .003, or .007 seconds

Default value

1900-01-01 00:00:00

Calendar

Gregorian (Does not include the complete range of years.)

User-defined fractional second precision

No

Time zone offset aware and preservation

No

Daylight saving aware

No

Supported String Literal Formats for datetime

The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. If the environment is not us_english, the string literals should be in the format N'string_literaL'.

Numeric

Description

Date formats:

[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)

Time formats:

14:30

14:30[:20:999]

14:30[:20.9]

4am

4 PM

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 that uses slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form:

  • number separator number separator number [time] [time]

When the language is set to us_english, the default order for the date is mdy. You can change the date order by using 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. A four-part year is interpreted as the year.

Alphabetical

Description

Apr[il] [15][,] 1996

Apr[il] 15[,] [19]96

Apr[il] 1996 [15]

[15] Apr[il][,] 1996

15 Apr[il][,][19]96

15 [19]96 apr[il]

[15] 1996 apr[il]

1996 APR[IL] [15]

1996 [15] APR[IL]

You can specify date data with a month specified as the full month name. For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored.

Here are some guidelines for using alphabetical date formats:

  • Enclose the date and time data in single quotation marks ('). For languages other than English, use N'

  • Characters that are enclosed in brackets are optional.

  • If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option 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 comes before 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 alphabetical form.

ISO 8601

Description

YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDD[ hh:mm:ss[.mmm]]

Examples:

  • 2004-05-23T14:25:10

  • 2004-05-23T14:25:10.487

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format.

The T indicates the start of the time part of the datetime value.

The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.

Unseparated

Description

YYYYMMDD hh:mm:ss[.mmm]

ODBC

Description

{ ts '1998-05-02 01:23:56.123' }

{ d '1990-10-02' }

{ t '13:33:41' }

The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB provider for SQL Server. Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }:

  • literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers:

    • d = date only

    • t = time only

    • ts = timestamp (time + date)

  • 'constant_value' is the value of the escape sequence. constant_value must follow these formats for each literal_type.

    literal_type

    constant_value format

    d

    yyyy-mm-dd

    t

    hh:mm:ss[.fff]

    ts

    yyyy-mm-dd hh:mm:ss[.fff]

Rounding of datetime Fractional Second Precision

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value

System stored value

01/01/98 23:59:59.999

1998-01-02 00:00:00.000

01/01/98 23:59:59.995

01/01/98 23:59:59.996

01/01/98 23:59:59.997

01/01/98 23:59:59.998

1998-01-01 23:59:59.997

01/01/98 23:59:59.992

01/01/98 23:59:59.993

01/01/98 23:59:59.994

1998-01-01 23:59:59.993

01/01/98 23:59:59.990

01/01/98 23:59:59.991

1998-01-01 23:59:59.990

ANSI and ISO 8601 Compliance

datetime is not ANSI or ISO 8601 compliant.

Converting Date and Time Data

When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

Converting datetime to Other Date and Time Types

The following table describes what occurs when a datetime data type is converted to other date and time data types.

Data type to convert to

Conversion details

date

The year month and day are copied. The time component is set to 00:00:00.000.

The following code shows the results of converting a date value to a datetime value.

DECLARE @date date = '12-21-05';
DECLARE @datetime datetime = @date;
SELECT @datetime AS '@datetime', @date AS '@date';
--Result
--@datetime               @date
------------------------- ----------
--2005-12-21 00:00:00.000 2005-12-21

time(n)

The time component is copied, and the date component is set to '1900-01-01'. When the fractional precision of the time(n) value greater than three digits, the value will be truncated to fit.

The following example shows the results of converting a time(4) value to a datetime value.

DECLARE @time time(4) = '12:10:05.1237';
DECLARE @datetime datetime = @time;
SELECT @datetime AS '@datetime', @time AS '@time';
--Result
--@datetime               @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1237
--
--(1 row(s) affected)

smalldatetime

The hours and minutes are copied. The seconds and fractional seconds are set to 0.

The following code shows the results of converting a smalldatetime value to a datetime value.

DECLARE @smalldatetime smalldatetime = '12-01-01 12:32';
DECLARE @datetime datetime = @smalldatetime;
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime';
--Result
--@datetime               @smalldatetime
------------------------- -----------------------
--2001-12-01 12:32:00.000 2001-12-01 12:32:00
--
--(1 row(s) affected)

datetimeoffset(n)

The date and time components are copied. The time zone is truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value will be truncated.

The following example shows the results of converting a datetimeoffset(4) value to a datetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '1968-10-23 12:45:37.1234 +10:0';
DECLARE @datetime datetime = @datetimeoffset;
SELECT @datetime AS '@datetime', @datetimeoffset AS '@datetimeoffset';
--Result
--@datetime               @datetimeoffset
------------------------- ------------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237 +01:0
--
--(1 row(s) affected)

datetime2(n)

The date and time are copied. When the fractional precision of the datetime2(n) value is greater than three digits, the value will be truncated.

The following example shows the results of converting a datetime2(4) value to a datetime value.

DECLARE @datetime2 datetime2(4) = '1968-10-23 12:45:37.1237';
DECLARE @datetime datetime = @datetime2;
SELECT @datetime AS '@datetime', @datetime2 AS '@datetime2';
--Result
--@datetime               @datetime2
------------------------- ------------------------
--1968-10-23 12:45:37.123 1968-10-23 12:45:37.1237
--
--(1 row(s) affected)

Examples

The following example compares the results of casting a string to each date and time data type.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Here is the result set.

Data type

Output

time

12:35:29. 1234567

date

2007-05-08

smalldatetime

2007-05-08 12:35:00

datetime

2007-05-08 12:35:29.123

datetime2

2007-05-08 12:35:29. 1234567

datetimeoffset

2007-05-08 12:35:29.1234567 +12:15

See Also

Reference

CAST and CONVERT (Transact-SQL)