Dela via

  • Artikel

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

To add and subtract for all date and time data types, useDATEADD and DATEDIFF.

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

No. (The date portion is not DATEFORMAT dependent.)

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]

Examples 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

  1. 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.

  2. 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.

  3. 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.

  4. 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.

DECLARE @date date;
DECLARE @datetime datetime;
SET @date = '12-10-25';
SET @datetime = @date;
SELECT @date AS '@date', @datetime AS '@datetime';
--Result
--@date      @datetime
------------ -----------------------
--2025-12-10 2025-12-10 00:00:00.000
--
--(1 row(s) affected)

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.

DECLARE @date date;
DECLARE @smalldatetime smalldatetime;
SET @date = '1912-10-25';
SET @smalldatetime = @date;
SELECT @date AS '@date', @smalldatetime AS '@smalldatetime';
--Result
--@date      @smalldatetime
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00
--
--(1 row(s) affected)

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.

DECLARE @date date;
DECLARE @datetimeoffset datetimeoffset(3);
SET @date = '1912-10-25';
SET @datetimeoffset = @date;
SELECT @date AS '@date', @datetimeoffset AS '@datetimeoffset';
--Result
--@date      @datetimeoffset
------------ ------------------------------
--1912-10-25 1912-10-25 00:00:00.000 +00:00
--
--(1 row(s) affected)

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.

DECLARE @date dat;e
DECLARE @datetime2 datetime2(3);
SET @date = '1912-10-25';
SET @datetime2 = @date;
SELECT @date AS '@date', @datetime2 AS '@datetime2(3)';
--Result
--@date      @datetime2(3)
------------ -----------------------
--1912-10-25 1912-10-25 00:00:00.000
--
--(1 row(s) affected)

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.

DECLARE @timeTo time(3);
DECLARE @timeFrom time(4);
SET @timeFrom = '12:34:54.1234';
SET @timeTo = @timeFrom;
SELECT @timeTo AS 'time(3)', @timeFrom AS 'time(4)';
--Results
--time(3)      time(4)
-------------- -------------
--12:34:54.123 12:34:54.1234
--
--(1 row(s) affected)

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.

DECLARE @time time(4);
DECLARE @datetime datetime;
SET @time = '12:15:04.1234';
SET @datetime = @time;
SELECT @time AS '@time', @datetime AS '@datetime';
--Result
--@time         @datetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:04.123
--
--(1 row(s) affected)

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.

DECLARE @time time(4);
DECLARE @smalldatetime smalldatetime;
SET @time = '12:15:04.1234';
SET @smalldatetime = @time;
SELECT @time AS '@time', @smalldatetime AS '@smalldatetime';
--Result
--@time         @smalldatetime
--------------- -----------------------
--12:15:04.1234 1900-01-01 12:15:00
--
--(1 row(s) affected)

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.

DECLARE @time time(4);
DECLARE @datetimeoffset datetimeoffset(3);
SET @time = '12:15:04.1234';
SET @datetimeoffset = @time;
SELECT @time AS '@time', @datetimeoffset AS '@datetimeoffset';
--Result
--@time         @datetimeoffset
--------------- ------------------------------
--12:15:04.1234 1900-01-01 12:15:04.123 +00:00
--
--(1 row(s) affected)

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.

DECLARE @time time(4);
DECLARE @datetime2 datetime2(3);
SET @time = '12:15:04.1234';
SET @datetime2 = @time;
SELECT @datetime2 AS '@datetime2', @time AS '@time';
--Result
--@datetime2              @time
------------------------- -------------
--1900-01-01 12:15:04.123 12:15:04.1234
--
--(1 row(s) affected)

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.

DECLARE @date date;
DECLARE @datetime datetime;
SET @date = '12-21-05';
SET @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);
DECLARE @datetime datetime;
SET @time = '12:10:05.1234';
SET @datetime = @time;
SELECT @datetime AS '@datetime', @time AS '@time';
--Result
--@datetime               @time
------------------------- -------------
--1900-01-01 12:10:05.123 12:10:05.1234
--
--(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;
DECLARE @datetime datetime;
SET @smalldatetime = '12-01-01 12:32';
SET @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);
DECLARE @datetime datetime;
SET @datetimeoffset = '1968-10-23 12:45:37.1234 +10:0';
SET @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.1234 +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);
DECLARE @datetime datetime;
SET @datetime2 = '1968-10-23 12:45:37.1237';
SET @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)

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.

DECLARE @smalldatetime smalldatetime;
DECLARE @date dat;e
SET @smalldatetime = '1955-12-13 12:43:10';
SET @date = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';
--Result
--@smalldatetime          date
------------------------- ----------
--1955-12-13 12:43:00     1955-12-13
--
--(1 row(s) affected)

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.

DECLARE @smalldatetime smalldatetime;
DECLARE @time time(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @time = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';
--Result
--@smalldatetime          time
------------------------- -------------
--1955-12-13 12:43:00     12:43:00.0000
--
--(1 row(s) affected)

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.

DECLARE @smalldatetime smalldatetime;
DECLARE @datetime datetime;
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetime = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';
--Result
--@smalldatetime          datetime
------------------------- -----------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.000
--
--(1 row(s) affected)

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.

DECLARE @smalldatetime smalldatetime;
DECLARE @datetimeoffset datetimeoffset(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetimeoffset = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)';
--Result
--@smalldatetime          datetimeoffset(4)
------------------------- ------------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000 +00:0
--
--(1 row(s) affected)

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.

DECLARE @smalldatetime smalldatetime;
DECLARE @datetime2 datetime2(4);
SET @smalldatetime = '1955-12-13 12:43:10';
SET @datetime2 = @smalldatetime;
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)';
--Result
--@smalldatetime           datetime2(4)
------------------------- ------------------------
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000
--
--(1 row(s) affected)

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.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @date date;
SET @datetimeoffset = '12-10-25 12:32:10 +01:0;
SET @date = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';
--Result
--@datetimeoffset                date
-------------------------------- ----------
--2025-12-10 12:32:10.0000 +01:0 2025-12-10
--
--(1 row(s) affected)

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.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @time time(3);
SET @datetimeoffset = '12-10-25 12:32:10.1234 +01:0';
SET @time = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';
--Result
--@datetimeoffset                time
-------------------------------- ------------
--2025-12-10 12:32:10.1234 +01:0 12:32:10.123
--
--(1 row(s) affected)

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.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @datetime datetime;
SET @datetimeoffset = '12-10-25 12:32:10.1234 +01:0';
SET @datetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';
--Result
--@datetimeoffset                datetime
-------------------------------- -----------------------
--2025-12-10 12:32:10.1234 +01:0 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

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.

DECLARE @datetimeoffset datetimeoffset(3);
DECLARE @smalldatetime smalldatetime;
SET @datetimeoffset = '1912-10-25 12:24:32 +10:0';
SET @smalldatetime = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';
--Result
--@datetimeoffset                @smalldatetime
-------------------------------- -----------------------
--1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00
--
--(1 row(s) affected)

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.

DECLARE @datetimeoffset datetimeoffset(4);
DECLARE @datetime2 datetime2(3);
SET @datetimeoffset = '1912-10-25 12:24:32.1234 +10:0';
SET @datetime2 = @datetimeoffset;
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';
--Result
--@datetimeoffset                @datetime2
-------------------------------- -----------------------
--1912-10-25 12:24:32.1234 +10:0 1912-10-25 12:24:32.123
--
--(1 row(s) affected)

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.

DECLARE @datetime2 datetime2(4;
DECLARE @date date;
SET @datetime2 = '12-10-25 12:32:10.1234';
SET @date = @datetime2;
SELECT @datetime2 AS '@datetime2', @date AS 'date';
--Result
--@datetime2               date
-------------------------- ----------
--2025-12-10 12:32:10.1234 2025-12-10
--
--(1 row(s) affected)

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.

DECLARE @datetime2 datetime2(4);
DECLARE @time time(3);
SET @datetime2 = '12-10-25 12:32:10.1234';
SET @time = @datetime2;
SELECT @datetime2 AS '@datetime2', @time AS 'time(3)';
--Result
--@datetime2                time(3) 
-------------------------- ------------
--2025-12-10 12:32:10.1234 12:32:10.123
--
--(1 row(s) affected)

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.

DECLARE @datetime2 datetime;2
DECLARE @datetime datetime;
SET @datetime2 = '12-10-25 12:32:10.1234567;'
SET @datetime = @datetime2;
SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

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.

DECLARE @datetime2 datetime2;
DECLARE @smalldatetime smalldatetime;
SET @datetime2 = '12-10-25 12:32:10.1234567';
SET @smalldatetime = @datetime2;
SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime';
--Result
--@datetime2                  @datetime
----------------------------- -----------------------
--2025-12-10 12:32:10.1234567 2025-12-10 12:32:10.123
--
--(1 row(s) affected)

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.

DECLARE @datetime2 datetime2(3);
DECLARE @datetimeoffset datetimeoffset(2);
SET @datetime2 = '12-10-25 12:32:10.1234567';
SET @datetimeoffset = @datetime2;
SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset(2)';
--Result
--@datetime2              @datetimeoffset(2)
------------------------- -----------------------------
--2025-12-10 12:32:10.123 2025-12-10 12:32:10.12 +00:00
--
--(1 row(s) affected)

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.
  1. 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 AdventureWorks2008R2 sample database. The DATEPART is used to group total sales by weekday, month, year, and year/month/weekday.

USE AdventureWorks2008R2;
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('2007','2008')
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);