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

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.

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

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.

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
  

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]
  

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.

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.

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-DD hh: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.

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.

Conversion Notes

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

DECLARE @date date
DECLARE @datetime datetime

SELECT @date = '12-10-25'
SELECT @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)

DECLARE @date date
DECLARE @smalldatetime smalldatetime

SELECT @date = '1912-10-25'
SELECT @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)

DECLARE @date date
DECLARE @datetimeoffset datetimeoffset(3)

SELECT @date = '1912-10-25'
SELECT @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)

DECLARE @date date
DECLARE @datetime2 datetime2(3)

SELECT @date = '1912-10-25'
SELECT @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

DECLARE @timeTo time(3)
DECLARE @timeFrom time(4)

SELECT @timeFrom = '12:34:54.1234'
SELECT @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)

DECLARE @time time(4)
DECLARE @datetime datetime

SELECT @time = '12:15:04.1234'
SELECT @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)

DECLARE @time time(4)
DECLARE @smalldatetime smalldatetime

SELECT @time = '12:15:04.1234'
SELECT @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)

DECLARE @time time(4)
DECLARE @datetimeoffset datetimeoffset(3)

SELECT @time = '12:15:04.1234'
SELECT @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)

DECLARE @time time(4)
DECLARE @datetime2 datetime2(3)

SELECT @time = '12:15:04.1234'
SELECT @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

DECLARE @date date
DECLARE @datetime datetime

SELECT @date = '12-21-05'
SELECT @datetime = @date

SELECT @datetime AS '@datetime', @date AS '@date'

--Result
--@datetime               @date
------------------------- ----------
--2005-12-21 00:00:00.000 2005-12-21

DECLARE @time time(4)
DECLARE @datetime datetime

SELECT @time = '12:10:05.1234'
SELECT @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)

DECLARE @smalldatetime smalldatetime
DECLARE @datetime datetime

SELECT @smalldatetime = '12-01-01 12:32'
SELECT @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)

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime datetime

SELECT @datetimeoffset = '1968-10-23 12:45:37.1234 +10:0'
SELECT @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)

DECLARE @datetime2 datetime2(4)
DECLARE @datetime datetime

SELECT @datetime2 = '1968-10-23 12:45:37.1237'
SELECT @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

DECLARE @smalldatetime smalldatetime
DECLARE @date date

SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @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)

DECLARE @smalldatetime smalldatetime
DECLARE @time time(4)

SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @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)

DECLARE @smalldatetime smalldatetime
DECLARE @datetime datetime

SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @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)

DECLARE @smalldatetime smalldatetime
DECLARE @datetimeoffset datetimeoffset(4)

SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @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)

DECLARE @smalldatetime smalldatetime
DECLARE @datetime2 datetime2(4)

SELECT @smalldatetime = '1955-12-13 12:43:10'
SELECT @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

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @date date

SELECT @datetimeoffset = '12-10-25 12:32:10 +01:0'
SELECT @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)

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @time time(3)

SELECT @datetimeoffset = '12-10-25 12:32:10.1234 +01:0'
SELECT @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)

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime datetime

SELECT @datetimeoffset = '12-10-25 12:32:10.1234 +01:0'
SELECT @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)

DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @smalldatetime smalldatetime

SELECT @datetimeoffset = '1912-10-25 12:24:32 +10:0'
SELECT @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)

DECLARE @datetimeoffset datetimeoffset(4)
DECLARE @datetime2 datetime2(3)

SELECT @datetimeoffset = '1912-10-25 12:24:32.1234 +10:0'
SELECT @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

DECLARE @datetime2 datetime2(4)
DECLARE @date date

SELECT @datetime2 = '12-10-25 12:32:10.1234'
SELECT @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)

DECLARE @datetime2 datetime2(4)
DECLARE @time time(3)

SELECT @datetime2 = '12-10-25 12:32:10.1234'
SELECT @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)

DECLARE @datetime2 datetime2
DECLARE @datetime datetime

SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @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)

DECLARE @datetime2 datetime2
DECLARE @smalldatetime smalldatetime

SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @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)

DECLARE @datetime2 datetime2(3)
DECLARE @datetimeoffset datetimeoffset(2)

SELECT @datetime2 = '12-10-25 12:32:10.1234567'
SELECT @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)

This section provides information about conversion between date and time data types.

Conversion to datetimeoffset

Conversion from datetimeoffset

Information Dropped in Conversion

Truncation in Conversion

Conversion of Fractional Seconds

Style Arguments for the CONVERT Function

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

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)

• 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

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

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

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