Using Date and Time Data
The following sections in this topic provide information about and examples for using the date and time data types and functions. For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
Using Operators with Date and Time Data Types
Using Date and Time Formats
String Literal Date and Time Formats
Unseparated String Format
ISO 8601 Format
Alphabetical Date Formats
Numeric Date Formats
Time Formats
ODBC Date-time Format
Converting date, time, datetime2, and datetimeoffset
Conversion Between String Literals and time(n), date, datetime2(n), and datetimeoffset(n)
Conversion Between Date and Time Data Types
Using CAST and CONVERT with time, date, datetime2, and datetimeoffset
Style Arguments for the CONVERT Functionq
SQL Server Date and Time Limitations
Backward Compatibility for Down-level Clients
Examples
Using Operators with Date and Time Data Types
The relational operators (<, <=, >, >=, <>), comparison operators (=, <, <=, >, >=, <>, !<, !>) and logical operators and Boolean predicates (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS, and LIKE) are supported for all the date and time data types.
Date and Time Arithmetic Operators
Using Date and Time Formats
String literal formats affect the presentation of data in applications to users but not the underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.
Some string literal formats are not affected by these settings. Consider using a format that does not depend on these settings, unless you know the settings are correct for the format. The ISO 8601 format does not depend on these settings and is an international standard. Transact-SQL that uses string literal formats, dependent on system settings, is less portable.
To find out the default string literal format for down-level clients, see the topic for each date and time data type. For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
The ydm date format is not supported for the date, datetime2 and datetimeoffset types. A run time error will be raised.
String Literal Date and Time Formats
The following table lists different date and time string formats. We recommend that you use date-time formats that are not DATEFORMAT dependent and are multilanguage. The ISO 8601 formats, '1998-02-23T14:23:05' and '1998-02-23T14:23:05-08:00' are the only formats that are an international standard. They are not DATEFORMAT or default login language dependent and are multilanguage.
Date-time part |
Format type |
Format example |
Can combine with other formats |
DATEFORMAT dependent |
Multilanguage |
|---|---|---|---|---|---|
Date |
Un-separated ISO 8601 |
'19980223' |
Yes |
No |
Yes |
Date |
Numeric |
'02/23/1998' |
Yes |
yes |
No (DATEFORMAT) |
Date |
ISO 8601 Numeric |
'1998-02-23' |
Yes |
No |
No |
Date |
Alphabetical |
'23 February 1998' |
Yes |
No |
No (month or short month) |
Date |
ODBC date |
{d '1998-02-23'} |
No |
No |
Yes |
Time |
ISO 8601 Time |
'14:23:05' '10:00:00.123456' |
Yes |
No |
Yes |
Time |
ODBC time |
{t '14:23:05'} |
No |
No |
Yes |
Date-time |
ODBC date-time |
{ts '1998-02-23 14:23:05'} |
No |
No |
Yes |
Date-time |
ISO 8601 |
'1998-02-23T14:23:05' '1998-02-23T14:23:05 -08:00' |
No |
No |
Yes date, datetime2, datetimeoffset. |
Date-time |
ANSI SQL Standard |
'1998-02-23 14:23:05' '1998-02-23 14:23:05 -08:00' |
No |
No (datetime2, datetimeoffset) Yes (datetime) |
Yes date, datetime2, datetimeoffset. |
Date-time |
Combination of date and time |
'19980223 14:23:05' '02/23/1998 2:23:05 PM' '1998-02-23 10:00:00.123' '23 Feb 1998 14:23:05' |
No |
Yes (date part) |
No |
TimeZone |
TimeZone Format |
'+12:00' '01:00' '-08:00' 'Z' |
Yes |
No |
Yes |
The following statements show the effects of SET LANGUAGE and SET DATEFORMAT settings.
DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
-- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: dic 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
SET LANGUAGE us_english;
-- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: Dec 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
-- Returns: Msg 241, Conversion failed when converting
-- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
Unseparated String Format
You can specify date data as an unseparated string. The date data can be specified by using four, six, or eight digits, an empty string, or a time value without a date value.
The SET DATEFORMAT session setting does not apply to all-numeric date entries, such as numeric entries without separators. The six-digit or eight-digit strings are always interpreted as ymd. The month and day must always be two digits.
This is the valid un-separated string format: [19]960415
A string of only four digits is interpreted as the year. The month and date are set to January 1. When you specify only four digits, you must include the century.
ISO 8601 Format
The ISO 8601 date with time format is as follows:
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)
The T indicates the start of the time part of the date-time value.
To use the ISO 8601 format, you must specify each element in the format. This includes the T, the colons (:), the + or - , and the periods (.). The brackets indicate that the fractional seconds or time zone offset components are optional.
The time component is specified in the 24-hour format.
The advantage in using the ISO 8601 format is that it is an international standard. Date and time values that are specified by using this format are unambiguous. This format is not affected by the SET DATEFORMAT, SET LANGUAGE, of login default language settings.
The following are two examples of date-time values that are specified in the ISO 8601 format:
2004-05-23T14:25:10
2004-05-23T14:25:10.1234567+07:00
Alphabetical Date Formats
You can specify a month as a name, for example, April or the abbreviation Apr in English. These should be specified in the LANGUAGE setting of the session, for example, avril or avr in French. 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 (').
If you specify only the last two digits of the year, values less than the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values that are 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.
The following formats are the valid alphabetical formats for SQL Server date data. Characters that are enclosed in brackets are optional.
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]
Numeric Date Formats
You can specify date data with a numeric month. For example, 5/20/97 represents the twentieth day of May 1997. When you use a numeric date format, specify the year, month, and day 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)
The default language DATEFORMAT for a session is set by the default language for the login, a SET LANGUAGE statement, or a SET DATEFORMAT statement. When the language is set to us_english by the default login or the SET LANGUAGE statement, 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-digit year will be interpreted as year.
Time Formats
SQL Server recognizes the following formats for time data. Enclose each format with single quotation marks (').
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
The following statements show the return values of different inputs to the CAST function.
SELECT CAST('01/01/2000 14:30' AS datetime2)
--Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
-- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
-- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
GO
You can specify a suffix of AM or PM to indicate if the time value is before or after 12 noon. The case of AM or PM is ignored.
Hours can be specified by using either a 12-hour or 24-hour clock. The hour values are interpreted as follows.
The hour value of 00 represents the hour after midnight (AM), regardless of whether you specify AM. You cannot specify PM when the hour equals 00.
Hour values from 01 through 11 represent the hours before noon if neither AM nor PM is specified. They also represent the hours before noon when AM is specified. They represent hours after noon if PM is specified.
The hour value 12 represents the hour that starts at noon if neither AM nor PM is specified. If AM is specified, it represents the hour that starts at midnight. If PM is specified, it represents the hour that starts at noon. For example: 12:01 is 1 minute after noon, as is 12:01 PM, while 12:01 AM is 1 minute after midnight. Specifying 12:01 AM is the same as specifying 00:01 or 00:01 AM.
Hour values from 13 through 23 represents hours after noon if AM or PM is not specified. They also represent the hours after noon when PM is specified. You cannot specify AM when the hour value is from 13 through 23.
An hour value of 24 is not valid; use 12:00 AM or 00:00 to represent midnight.
Milliseconds can be preceded by either a colon (:) or a period (.). If preceded by a colon, the number means thousandths-of-a-second. If preceded by a period, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, 12:30:20:1 indicates twenty and one-thousandth seconds past 12:30; 12:30:20.1 indicates twenty and one-tenth seconds past 12:30.
ODBC Date-time Format
The ODBC API defines escape sequences to represent date and time values that ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) and 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.
SQL Server always treats ODBC data as being of the datetime data type.
ODBC timestamp escape sequences are of the format:
{ literal_type 'constant_value' }
literal_type
Specifies the type of the escape sequence. The following are the valid arguments for literal_type.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-DDhh:mm:ss[.fff]
TExamples of ODBC time and date constants are as follows:
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }
Do not confuse the ODBC and OLE DB timestamp data type name with the Transact-SQL timestamp data type name. The ODBC and OLE DB timestamp data type records dates and times. The Transact-SQL timestamp data type is a binary data type that has no time-related values.
Converting date, time, datetime2, and datetimeoffset
There are two kinds of conversions between different date types: explicit and implicit. Implicit conversions occur without using the CAST or CONVERT functions. Explicit conversions require the CAST or CONVERT functions.
Conversion Between String Literals and time(n), date, datetime2(n), and datetimeoffset(n)
Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. Otherwise, a runtime error is raised.
Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session.
For implicit conversion from date, time, datetime2 and datetimeoffset data types to character strings, the SQL standard format YYY-MM-DD hh:mi:ss.[nnnnnnn], CONVERT style 121, will be applied. The CONVERT style 0 format, mon dd yyyy hh:miAM (or PM), is applied for datetime and smalldatetime data types.
The following table shows the rules for conversion between date, time, datetime2 and datetimeoffset types and string literals.
Input string literal |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|
ODBC DATE |
See note 1. |
See note 1. |
See note 1. |
See note 1. |
ODBC TIME |
See note 1. |
See note 1. |
See note 1. |
See note 1. |
ODBC DATETIME |
See note 1. |
See note 1. |
See note 1. |
See note 1. |
DATE only |
Trivial |
Default values are supplied |
The TIME part defaults to 00:00:00. |
The TIME part defaults to 00:00:00. The TIMEZONE defaults to +00:00. |
TIME only |
Default values are supplied |
Trivial |
The DATE part defaults to 1900-1-1. |
The DATE part defaults to 1900-1-1. The TIMEZONE will default to +00:00. |
TIMEZONE only |
Default values are supplied |
Default values are supplied |
Default values are supplied |
Default values are supplied |
DATE + TIME |
The DATE part of the input string is used. |
The TIME part of the input string is used. |
Trivial |
The TIMEZONE defaults to +00:00. |
DATE + TIMEZONE |
Not allowed |
Not allowed |
Not allowed |
Not allowed |
TIME + TIMEZONE |
Default values are supplied |
The TIME part of the input string is used. |
The DATE part defaults to 1900-1-1. TIMEZONE input is ignored. |
The DATE part defaults to 1900-1-1. |
DATE + TIME + TIMEZONE |
The DATE part of local DATETIME will be used. |
The TIME part of local DATETIME will be used. |
The local DATETIME will be used. |
Trivial |
Conversion Notes
ODBC string literals are mapped to the datetime data type. Any assignment operation from ODBC DATETIME literals into date, time, datetime2, or datetimeoffset types will cause an implicit conversion between datetime and these types as defined by the conversion rules.
The fractional seconds precision of datetime has an accuracy of one three-hundredths of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds. '08/22/1995 10:15:19:999' is rounded because '.999' exceeds the precision.
For time(3), datetime2(3) or datetimeoffset(3), the fractional seconds precision has an accuracy of one millisecond. Therefore, '1995-8-22 10:15:19:999' will not be rounded.
The input TIMEZONE offset part should be always be double digits for both hh and mm. The sign, either + or –, is mandatory.
Conversion Between Date and Time Data Types
The tables in this section describe how each of the following date and time data types is converted to the other date and time data types:
date
time(n)
datetime
smalldatetime
datetimeoffset(n)
datetime2
date Data Type
The following table describes what occurs when a date data type is converted to other date and time data types.
Data type to convert to |
Conversion details |
|---|---|
time(n) |
The conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time". |
datetime |
The date is copied. The following code shows the results of converting a date value to a datetime value. |
smalldatetime |
When the date value is in the range of a smalldatetime, the date component is copied and the time component is set to 00:00:00.000. When the date value is outside the range of a smalldatetime value, error message 242 is raised: "The conversion of a date data type to a smalldatetime data type resulted in an out-of-range value.";and the smalldatetime value is set to NULL. The following code shows the results of converting a date value to a smalldatetime value. |
datetimeoffset(n) |
The date is copied, and the time is set to 00:00.0000000 +00:00. The following code shows the results of converting a date value to a datetimeoffset(3) value. |
datetime2(n) |
The date component is copied, and the time component is set to 00:00.000000. The following code shows the results of converting a date value to a datetime2(3) value. |
time(n) Data Type
The following table describes what occurs when a time data type is converted to other date and time data types.
Data type to convert to |
Conversion details |
|---|---|
time(n) |
The hour, minute, and seconds are copied. When the destination precision is less than the source precision, the fractional seconds will be truncated to fit the destination precision. The following example shows the results of converting a time(4) value to a time(3) value. |
date |
The conversion fails, and error message 206 is raised: "Operand type clash: date is incompatible with time". |
datetime |
The hour, minute, and second values are copied; and the date component is set to '1900-01-01'. When the fractional seconds precision of the time(n) value is greater than three digits, the datetime result will be truncated. The following code shows the results of converting a time(4) value to a datetime value. |
smalldatetime |
The date is set to '1900-01-01', and the hour and minute values are copied. The seconds and fractional seconds are set to 0. The following code shows the results of converting a time(4) value to a smalldatetime value. |
datetimeoffset(n) |
The date is set to '1900-01-01', and the time is copied. The time zone offset is set to +00:00. When the fractional seconds precision of the time(n) value is greater than the precision of the datetimeoffset(n) value, the value is truncated to fit. The following example shows the results of converting a time(4) value to a datetimeoffset(3) type. |
datetime2(n) |
The date is set to '1900-01-01', the time component is copied, and the time zone offset is set to 00:00. When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value will be truncated to fit. The following example shows the results of converting a time(4) value to a datetime2(2) value. |
datetime Data Type
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. |
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. |
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. |
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. |
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. |
smalldatetime Data Type
The following table describes what occurs when a smalldatetime 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 following code shows the results of converting a smalldatetime value to a date value. |
time(n) |
The hours, minutes, and seconds are copied. The fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a time(4) value. |
datetime |
The smalldatetime value is copied to the datetime value. The fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime value. |
datetimeoffset(n) |
The smalldatetime value is copied to the datetimeoffset(n) value. The fractional seconds are set to 0, and the time zone offset is set to +00:0. The following code shows the results of converting a smalldatetime value to a datetimeoffset(4) value. |
datetime2(n) |
The smalldatetime value is copied to the datetime2(n) value. The fractional seconds are set to 0. The following code shows the results of converting a smalldatetime value to a datetime2(4) value. |
datetimeoffset(n) Data Type
The following table describes what occurs when a datetimeoffset(n) 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 following code shows the results of converting a datetimeoffset(4) value to a date value. |
time(n) |
The hour, minute, second, and fractional seconds are copied. The time zone value is truncated. When the precision of the datetimeoffset(n) value is greater than the precision of the time(n) value, the value is truncated. The following code shows the results of converting a datetimeoffset(4) value to a time(3) value. |
datetime |
The date and time values are copied, and the time zone is truncated. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value is truncated. The following code shows the results of converting a datetimeoffset(4) value to a datetime value. |
smalldatetime |
The date, hours, and minutes are copied. The seconds are set to 0. The following code shows the results of converting a datetimeoffset(3) value to a smalldatetime value. |
datetime2(n) |
The date and time are copied to the datetime2 value, and the time zone is truncated. When the precision of the datetime2(n) value is greater than the precision of the datetimeoffset(n) value, the fractional seconds are truncated to fit. The follow code shows the results of converting a datetimeoffset(4) value to a datetime2(3) value. |
datetime2 Data Type
The following table describes what occurs when a datetime2 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 following code shows the results of converting a datetime2(4) value to a date value. |
time(n) |
The hour, minute, second, and fractional seconds are copied. The following code shows the results of converting a datetime2(4) value to a time(3) value. |
datetime |
The date and time values are copied. When the fractional precision of the datetimeoffset(n) value is greater than three digits, the value is truncated. The following code shows the results of converting a datetime2 value to a datetime value. |
smalldatetime |
The date, hours, and minutes are copied. The seconds are set to 0. The following code shows the results of converting a datetime2 value to a smalldatetime value. |
datetimeoffset(n) |
The datetime2(n) value is copied to the datetimeoffset(n) value. The time zone offset is set to +00:0. When the precision of the datetime2(n) value is greater than the precision of datetimeoffset(n) value, the value is truncated to fit. The following code shows the results of converting a datetime2(5) value to a datetimeoffset(3) value. |
Using CAST and CONVERT with time, date, datetime2, and datetimeoffset
This section provides information about conversion between date and time data types.
Conversion to datetimeoffset
- Whenever a datetimeoffset value with time zone is to be implicitly derived from a value without a time zone (for example, in a simple assignment operation), the value without time zone is treated as local, and the current default time zone (00:00) displacement is subtracted from it to give UTC.
- The UTC time zone (00:00) is always appended when you convert non–time zone data types to datetimeoffset for the following conversions:
date to datetimeoffset
time to datetimeoffset
datetime2 to datetimeoffset
datetime or smalldatetime to datetimeoffset
String literals in valid date, time, or datetime without time zone formats to datetimeoffset
Conversion from datetimeoffset
When you convert from datetimeoffset to the following non–time zone types, style 0 (default) always indicates that the return date, time, datetime2, datetime, or smalltime value is in local format of the preserved time zone offset; and style 1 always indicates UTC format.
Whenever a date or time value without time zone is implicitly derived in one of the following conversions, the datetimeoffset value is treated as UTC. The preserved time zone displacement is added to the value to give local time. The result, without any time zone offset, is in local time.
datetimeoffset to date
datetimeoffset to time
datetimeoffset to datetime2
datetimeoffset to datetime or smalldatetime
The 0 and 1 styles cannot be used for datetimeoffset to string conversion. Instead, first convert from datetimeoffset to datetime2 or datetime, and thento varchar or char.
If an existing CONVERT style includes the time part, and the conversion is from datetimeoffset to string, the time zone offset (except for style 127) is included. If you do not want the time zone offset, you can CAST to datetime2 first and then to string.
All existing date and time styles will apply to any datetimeoffset to string conversion and the time-zone offset will be retained.
The input string time zone offset portion should be always double digits for both hh and mm, and the sign, either + or – is mandatory.
Information Dropped in Conversion
When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly. For datetimeoffset, the extraction is performed on the local date and time but not the UTC value.
For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value (to the persistent time zone offset). For example, when the datetimeoffset(3), value, 2006-10-21 12:20:20.999 -8:00, is converted to time(3), the result is 12:20:20.999 not 20:20:20.999(UTC).
Truncation in Conversion
- Conversions from a higher-precision time values to a lower-precision values are permitted. The higher-precision values will be truncated to fit the lower precision type.
Conversion of Fractional Seconds
If a style includes the time format hh:mm:ss.mmm, the format will become hh:mm:ss.[nnnnnnn] for time(n), datetime2(n) and datetimeoffset(n). The number of digits depends on the type specification. If you want only millisecond precision, convert to datetime2(3) first, and then to string.
For styles 9, 109, 13, 113, 21, 121, 130, and 131, the colon (:) fractional seconds preceding separator is not supported for time, datetime2 and datetimeoffset types for string conversion. The output string format with any of these styles will be transformed to a period (.).
Style Arguments for the CONVERT Function
The following table lists examples of date, time, datetime2, and datetimeoffset values for style arguments for the CONVERT function. For more information about style, see the "Arguments" section of CAST and CONVERT (Transact-SQL)
Style |
Associated standard |
Input/Output (3) format |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|---|---|
0 or 100 (1,2) |
Default |
mon dd yyyy hh:miAM (or PM) |
Jan 1 2001 |
12:20PM |
Jan 1 2001 12:20PM |
Jan 1 2001 12:20PM -08:00 |
101 |
U.S. |
mm/dd/yyyy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
102 |
ANSI |
yy.mm.dd |
2001.01.01 |
- |
2001.01.01 |
2001.01.01 |
103 |
British/French |
dd/mm/yy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
104 |
German |
dd.mm.yy |
01.01.2001 |
- |
01.01.2001 |
01.01.2001 |
105 |
Italian |
dd-mm-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
106(1) |
- |
dd mon yy |
01 Jan 2001 |
- |
01 Jan 2001 |
01 Jan 2001 |
107(1) |
- |
Mon dd, yy |
Jan 01, 2001 |
- |
Jan 01, 2001 |
Jan 01, 2001 |
108 |
- |
hh:mi:ss |
- |
12:20:20 |
12:20:20 |
12:20:20 |
9 or 109 (1,2) |
Default + milliseconds |
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
Jan 1 2001 |
12:20:20.1234567AM |
Jan 1 2001 12:20:20.1234567PM |
Jan 1 2001 12:20:20:1230000PM -08:00 |
110 |
United States |
mm-dd-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
111 |
JAPAN |
yy/mm/dd |
2001/01/01 |
- |
2001/01/01 |
2001/01/01 |
112 |
ISO |
yymmdd |
20010101 |
- |
20010101 |
20010101 |
13 or 113 (1,2) |
Europe default + milliseconds |
dd mon yyyy hh:mi:ss:mmm(24h) |
01 Jan 2001 |
12:20:20.1234567 |
01 Jan 2001 12:20:20.1234567 |
01 Jan 2001 12:20:20:1230000 -08:00 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
12:20:20.1234567 |
12:20:20.1234567 |
12:20:20:1230000 -08:00 |
20 or 120 (2) |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
2001-01-01 |
12:20:20 |
2001-01-01 12:20:20 |
2001-01-01 12:20:20 -08:00 |
21 or 121 (2) |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01 12:20:20.1234567 |
2001-01-01 12:20:20.1230000 -08:00 |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
127(6, 7) |
ISO8601 with time zone Z. |
yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
2001-01-01 |
12:20:20.1234567Z |
2001-01-01T 12:20:20.1234567Z |
2001-01-01T20:20:20.1230000Z |
130 (1,2) |
Hijri (5) |
dd mon yyyy hh:mi:ss:mmmAM |
01 Jan 2001 |
12:20:20.1230000PM |
01 Jan 2001 12:20:20.1230000PM |
1 Jan 2001 12:20:20:1230000PM -08:00 |
131 (2) |
Hijri (5) |
dd/mm/yy hh:mi:ss:mmmAM |
01/01/2001 |
12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM -08:00 |
1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.
2 The default values (style0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
3 Input when you convert to datetime; output when you convert to character data.
4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.
5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.
6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0.
SQL Server Date and Time Limitations
In the following list, date and time refer to any date-time data type that includes a date or time part.
No server-side daylight saving time (DST) support for datetimeoffset.
No Julian calendar support for date.
No time support of '24' hour representation for midnight.
No time support of 'leap second' support over '59'.
No time support of 'one nanosecond' or greater for fractional second precision.
No time zone support for time.
No SQL Standard INTERVAL operation support.
Backward Compatibility for Down-level Clients
Some down-level clients do not supportthe time, date, datetime2 and datetimeoffset data types added in SQL Server 2008. The following table shows the type mapping between an up-level instance of SQL Server 2008 and down-level clients.
SQL Server 2008 data type |
Default string literal format passed to down-level client |
Down-level ODBC |
Down-level OLEDB |
Down-level JDBC |
Down-level SQLCLIENT |
|---|---|---|---|---|---|
time |
hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR or SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String or SqString |
date |
YYYY-MM-DD |
SQL_WVARCHAR or SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String or SqString |
datetime2 |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR or SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String or SqString |
datetimeoffset |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
SQL_WVARCHAR or SQL_VARCHAR |
DBTYPE_WSTRor DBTYPE_STR |
Java.sql.String |
String or SqString |
Examples
A. Comparing date and time data types
The following example compares the results of casting a string literal to each date and time data type. Trying to CAST a string literal with a fractional seconds precision that is more than that allowed for smalldatetime or datetime will raise an error.
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';
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(7) |
2007-05-08 12:35:29.1234567 |
datetimeoffset(7) |
2007-05-08 12:35:29.1234567 +12:15 |
B. Getting the current system date and time
The following example shows how to use SQL Server system functions that return the current date and time.
SELECT SYSDATETIME() AS 'SYSDATETIME'
--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)
SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)
SELECT GETDATE() AS 'GETDATE'
--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)
SELECT GETUTCDATE() AS 'GETUTCDATE'
--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)
C. Searching for all datetime2 values in a day
- The following example shows how to search for all date and time values in a day.
-- Create a table that contains with the following dates:
-- The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
-- The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
-- The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
-- The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');
-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate
FROM #Search
WHERE CONVERT(date,MyDate) = '2005-04-07';
--Use >= and <=.
SELECT MyDate
FROM #Search
WHERE MyDate >= '2005-04-07 00:00:00.0000000'
AND MyDate <= '2005-04-07 23:59:59.9999999';
--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
AND MyDate < '2005-04-08 00:00:00.0000000';
--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO
D. Searching for a time period in a day
The following examples show how to search for dates that have time values to find a time range.
-- Create a table called Search and insert
-- five different time values for the same
-- date.
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');
-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.
--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(0),MyDate) = '09:00:00';
--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000
--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) = '09:00:00';
--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000
--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.
--Use CONVERT with >= and <=.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) >= '09:00:00.0000000'
AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'
--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
AND CONVERT(time(7),MyDate) < '17:00:00.0000000';
--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
AND '16:59:59.9999999';
DROP TABLE #Search
GO
E. Using DATEPART and DATEADD to find the first and last days of a datepart
The following example shows how to return the first or last day of a datepart.
-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
-- year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
-- to obtain the first day of the next month.
-- DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);
--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;
-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);
-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);
-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);
-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);
F. Using user-defined datepart arguments for DATEADD, DATEDIFF, DATENAME, and DATEPART
The following example creates a scalar user-defined function that adds a constant to any part of a datetime2 value.
USE tempdb
GO
CREATE FUNCTION UserDateadd
(
@datepart nvarchar(11)
,@number int
,@date datetime2
)
RETURNS datetime2
AS
BEGIN
DECLARE @Return datetime2
SELECT @Return = CASE @datepart
WHEN 'year' THEN DATEADD(year,@number,@date)
WHEN 'yy' THEN DATEADD(year,@number,@date)
WHEN 'yyyy' THEN DATEADD(year,@number,@date)
WHEN 'quarter' THEN DATEADD(quarter,@number,@date)
WHEN 'qq' THEN DATEADD(quarter,@number,@date)
WHEN 'month' THEN DATEADD(month,@number,@date)
WHEN 'mm' THEN DATEADD(month,@number,@date)
WHEN 'm' THEN DATEADD(month,@number,@date)
WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date)
WHEN 'dy' THEN DATEADD(dayofyear,@number,@date)
WHEN 'y' THEN DATEADD(dayofyear,@number,@date)
WHEN 'day' THEN DATEADD(day,@number,@date)
WHEN 'dd' THEN DATEADD(day,@number,@date)
WHEN 'd' THEN DATEADD(day,@number,@date)
WHEN 'week' THEN DATEADD(week,@number,@date)
WHEN 'wk' THEN DATEADD(week,@number,@date)
WHEN 'ww' THEN DATEADD(week,@number,@date)
WHEN 'weekday' THEN DATEADD(weekday,@number,@date)
WHEN 'wk' THEN DATEADD(weekday,@number,@date)
WHEN 'w' THEN DATEADD(weekday,@number,@date)
WHEN 'hour' THEN DATEADD(hour,@number,@date)
WHEN 'hh' THEN DATEADD(hour,@number,@date)
WHEN 'minute' THEN DATEADD(minute,@number,@date)
WHEN 'mi' THEN DATEADD(minute,@number,@date)
WHEN 'n' THEN DATEADD(minute,@number,@date)
WHEN 'second' THEN DATEADD(second,@number,@date)
WHEN 'ss' THEN DATEADD(second,@number,@date)
WHEN 's' THEN DATEADD(second,@number,@date)
WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date)
WHEN 'ms' THEN DATEADD(millisecond,@number,@date)
WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
END
return @Return
END
GO
SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO
G. Using DATEPART to group by parts of a date
The following example uses the AdventureWorks sample database. The DATEPART is used to group total sales by weekday, month, year, and year/month/weekday.
USE AdventureWorks
GO
SELECT SUM(TotalDue) AS 'Total Sales'
,DATEPART(year,OrderDate)AS 'By Year'
,DATEPART(month,OrderDate) AS 'By Month'
,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2003','2004')
GROUP BY GROUPING SETS
(
(DATEPART(year,OrderDate))
,(DATEPART(month,OrderDate))
,(DATEPART(weekday,OrderDate))
,(
DATEPART(year,OrderDate)
,DATEPART(month,OrderDate),
DATEPART(weekday,OrderDate))
)
ORDER BY DATEPART(year,OrderDate)
,DATEPART(month,OrderDate)
,DATEPART(weekday,OrderDate)
See Also