Click here to Skip to main content
Click here to Skip to main content

Date and Time Data Types and Functions - SQL Server (2000, 2005, 2008, 2008 R2, 2012)

, 2 May 2013
Rate this:
Please Sign up or sign in to vote.
SQL Server 2000, 2005, 2008, 2008 R2, 2012 Date and Time Data Types and Functions with Sample SQL Statements. Data Type Tables, Function Tables

Introduction  

It would be better, if we know the different functions available with SQL Server for date and time. So that we can make use of functions properly in different scenarios. In this article we will go through all the available date and time data types and functions with sample codes. This article will list down the available date and time data types and functions for SQL Server version from 2000 and above.  If you want more info on any items then click on the 'read more details' of that section and that link will take you to MSDN site.   

Table Of Contents  

Background

The main objective of this article is to give a common place for all date and time data types and functions for all SQL Server versions so that we don't need to jump from one page to another. This article will help you to understand the different functions available with different versions of SQL Server with simple examples (All examples are self descriptive). At the same time this article can be used as a quick reference too.  

I have attached all the scripts used in this article as a .zip file. 

Date and Time Data Types - Quick Reference  

The Transact-SQL date and time data types are listed in the following table.  Click on the table header links to read more from MSDN. 

Date and Time Functions - SQL Server 2000, 2005, 2008, 2008 R2, 2012 

Data Type  2000  2005   2008   2008 R2   2012  
time  N   Y   Y   Y  
date  N   Y   Y   Y  
smalldatetime  Y   Y   Y   Y  
datetime  Y   Y   Y   Y  
datetime2  N   Y   Y   Y  
datetimeoffset  N   Y   Y  

To get data type metadata, see sys.systypes or TYPEPROPERTY. Precision and scale are variable for some date and time data types. To obtain the precision and scale for a column, see COLUMNPROPERTY, COL_LENGTH, or sys.columns. Below section describes all the above mentioned data types with short description, syntax and sample implementation.  

Date and Time Data Types - In Detail   

time 

Defines a time of a day without time zone awareness and is based on a 24-hour clock. Click here to read more from msdn.   

Syntax :- 

time [ (fractional second precision) ] 

Where 'fractional seconds precision' specifies the number of digits for the fractional part of the seconds. its default by 7 and can be from 0 to 7. 

Example :- 

DECLARE @time4 time(4) = '12:32:51.1234'; 
DECLARE @time7 time(7) = '23:32:51.1234567'; 
DECLARE @timeC time = '1955-12-13 19:21:55.123'--will only take the time and second fraction as 7  

SELECT @time4 AS 'time(4)', @time7 AS 'time(7)', @timeC AS 'timeC'   

Output :-  

time(4)		time(7)			timeC
------------	---------------		---------------
12:32:51.1234	23:32:51.1234567	19:21:55.1230000 

If we try to create a time variable or column with an invalid fractional second precision like below,

DECLARE @time8 time(8) = '11:33:44.12345678'; 

then we will get an error message as invalid,

Msg 1002, Level 15, State 1, Line 1
Line 1: Specified scale 8 is invalid.  

date 

Defines a date. Click here to read more from msdn.  

Syntax :- 

date  

The default string literal format as 'YYYY-MM-DD'  

Example :- 

DECLARE @date1 date= '11-21-38'; 
DECLARE @date2 date= '12-29-33 23:20:51'; -- will take only the date  

SELECT @date1 AS 'date1', @date2 AS 'date2'  

 Output :-

date1		date2
----------	----------
2038-11-21	2033-12-29 

smalldatetime

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. Second values can be any value ranging from 00 to 59,Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute. So the accuracy will be always one minute. Click here to read more from msdn.  

Syntax :-

smalldatetime  

Example :-

DECLARE @smalldatetime1 smalldatetime = '1955-12-13 12:43:31'; -- will round to next minute 
DECLARE @smalldatetime2 smalldatetime = '1955-12-13 12:43:29'; -- will not round to next minute  

SELECT @smalldatetime1 AS '@smalldatetime1', @smalldatetime2 AS '@smalldatetime2';  

Output :-

@smalldatetime1		@smalldatetime2
-------------------	-------------------
1955-12-13 12:44:00	1955-12-13 12:43:00 

datetime

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. Click here to read more from msdn.  

Syntax :-

datetime 

Example :-

DECLARE @datetime1 datetime = '12-23-35'; 
DECLARE @time1 time(4) = '11:10:05.1234'; 
DECLARE @datetime2 datetime = @time1   
 
SELECT @datetime1 AS 'datetime1', @datetime2 AS 'datetimevar2'  

Output :-

datetime1		datetimevar2
-----------------------	-----------------------
2035-12-23 00:00:00.000	1900-01-01 11:10:05.123 

datetime2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. Click here to read more from msdn.   

Syntax :-

datetime2 [ (fractional seconds precision) ]

Example :-

DECLARE @datetime2_1 datetime2(4) = '12-13-25 12:32:10.1234'; 
DECLARE @datetime2_2 datetime2 = '12-13-25 11:32:10.1234567'; 
DECLARE @datetime2_3 datetime2 = '12-13-25';   
 
SELECT @datetime2_1 AS 'datetime2_1', @datetime2_2 AS 'datetime2_2', @datetime2_3 AS 'datetime2_3';  

Output :-

datetime2_1			datetime2_2			datetime2_3
-----------------------		--------------------------	--------------------------
2025-12-13 12:32:10.1234	2025-12-13 11:32:10.1234567	2025-12-13 00:00:00.0000000 

datetimeoffset

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. Click here to read more from msdn.    

Syntax :-

datetimeoffset [ (fractional seconds precision) ] 

Example :-

DECLARE @datetimeoffset datetimeoffset(4) = '12-13-25 12:32:10 +05:30'; 
DECLARE @time time(3) = @datetimeoffset; 
DECLARE @date date= @datetimeoffset;   
 
SELECT @datetimeoffset AS 'datetimeoffset ', @date AS 'date', @time AS 'time';   

Output :- 

datetimeoffset 			date		time
-------------------------------	----------	------------
2025-12-13 12:32:10.0000 +05:30	2025-12-13	12:32:10.000   

Date and Time Functions - Quick Reference.  

This section will give you a quick reference over the available date and time functions in different versions of SQL Server. For ease of understanding I have grouped similar versions together, which having a same set of date and time functions. If you are not particularly looking for a specified version then click here to see 'date and time functions' in a single (combined) table.    

Function  Description   
DATEADD()  Returns a new datetime value based on adding an interval to the specified date. 
DATEDIFF()  Returns the number of date and time boundaries crossed between two specified dates. 
DATENAME()  Returns a character string representing the specified datepart of the specified date. 
DATEPART()  Returns an integer representing the specified datepart of the specified date. 
DAY()  Returns an integer representing the day datepart of the specified date. 
GETDATE()  Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. 
CURRENT_TIMESTAMP  Returns the current date and time. This function is equivalent to GETDATE(). 
GETUTCDATE()  Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. 
MONTH()  Returns an integer that represents the month part of a specified date. 
YEAR()  Returns an integer that represents the year part of a specified date. 
ISDATE()  Determines whether an input expression is a valid date. 
@@DATEFIRST  Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 3 for Wednesday, and so on through 7 for Sunday. 
SET DATEFIRST  Sets the first day of the week to a number from 1 through 7  
SET DATEFORMAT  Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 

Function  Description   
SYSDATETIME()  Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. 
SYSDATETIMEOFFSET()  Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included. 
SYSUTCDATETIME()  Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits. 
SWITCHOFFSET()  Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. 
TODATETIMEOFFSET()  Returns a datetimeoffset value that is translated from a datetime2 expression. 
DATEADD()  Returns a new datetime value based on adding an interval to the specified date. 
DATEDIFF()  Returns the number of date and time boundaries crossed between two specified dates. 
DATENAME()  Returns a character string representing the specified datepart of the specified date. 
DATEPART()  Returns an integer representing the specified datepart of the specified date. 
DAY()  Returns an integer representing the day datepart of the specified date. 
GETDATE()  Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. 
CURRENT_TIMESTAMP  Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE. 
GETUTCDATE()  Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. 
MONTH()  Returns an integer that represents the month part of a specified date. 
YEAR()  Returns an integer that represents the year part of a specified date. 
ISDATE()  Determines whether an input expression is a valid date. 
@@DATEFIRST  Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 3 for Wednesday, and so on through 7 for Sunday. 
SET DATEFIRST  Sets the first day of the week to a number from 1 through 7  
SET DATEFORMAT  Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 

Function  Description   
DATEFROMPARTS()  Returns a date value for the specified year, month, and day. 
DATETIME2FROMPARTS()  Returns a datetime2 value for the specified date and time and with the specified precision. 
DATETIMEFROMPARTS() 

Returns a datetime value for the specified date and time. 

DATETIMEOFFSETFROMPARTS()  Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision. 
SMALLDATETIMEFROMPARTS()  Returns a smalldatetime value for the specified date and time. 
TIMEFROMPARTS() 

Returns a time value for the specified time and with the specified precision. 

SYSDATETIME()  Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. 
SYSDATETIMEOFFSET()  Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included. 
SYSUTCDATETIME()  Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits. 
SWITCHOFFSET()  Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. 
TODATETIMEOFFSET()  Returns a datetimeoffset value that is translated from a datetime2 expression. 
DATEADD()  Returns a new datetime value based on adding an interval to the specified date. 
DATEDIFF()  Returns the number of date and time boundaries crossed between two specified dates. 
DATENAME()  Returns a character string representing the specified datepart of the specified date. 
DATEPART()  Returns an integer representing the specified datepart of the specified date. 
DAY()  Returns an integer representing the day datepart of the specified date. 
GETDATE()  Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. 
CURRENT_TIMESTAMP  Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE. 
GETUTCDATE()  Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. 
MONTH()  Returns an integer that represents the month part of a specified date. 
YEAR()  Returns an integer that represents the year part of a specified date. 
ISDATE()  Determines whether an input expression is a valid date. 
@@DATEFIRST  Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 3 for Wednesday, and so on through 7 for Sunday. 
SET DATEFIRST  Sets the first day of the week to a number from 1 through 7  
SET DATEFORMAT  Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 

Below table gives a combined view of different SQL Server's date and time functions. Click on version number in table header to read more from msdn. 

Date and Time Functions - SQL Server 2000, 2005, 2008, 2008 R2, 2012  

Function  Description    2000 2005 2008 2008R2 2012
GETDATE()  Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. 
CURRENT_TIMESTAMP  Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE Y Y Y
GETUTCDATE()  Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. 
SYSDATETIME()  Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.  N   N  
SYSDATETIMEOFFSET()  Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.  N   N  
SYSUTCDATETIME()  Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.  N  N  
DATENAME()  Returns a character string representing the specified datepart of the specified date. 
DATEPART()  Returns an integer representing the specified datepart of the specified date. 
DAY()  Returns an integer representing the daYdatepart of the specified date. 
MONTH()  Returns an integer that represents the month part of a specified date. 
YEAR()  Returns an integer that represents the year part of a specified date. 
DATEADD()  Returns a new datetime value based on adding an interval to the specified date. 
DATEDIFF()  Returns the number of date and time boundaries crossed between two specified dates. 
ISDATE()  Determines whether an input expression is a valid date. 
EMONTH()  Returns the last day of the month that contains the specified date, with an optional offset.  N  N  N  N 
SWITCHOFFSET()  Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.  N  N 
TODATETIMEOFFSET()  Returns a datetimeoffset value that is translated from a datetime2 expression.  N  N 
DATEFROMPARTS()  Returns a date value for the specified year, month, and day.  N  N  N  N 
DATETIME2FROMPARTS()  Returns a datetime2 value for the specified date and time and with the specified precision.  N  N  N  N 
DATETIMEFROMPARTS() 

Returns a datetime value for the specified date and time. 

N  N  N  N 
DATETIMEOFFSETFROMPARTS()  Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.  N  N  N  N 
SMALLDATETIMEFROMPARTS()  Returns a smalldatetime value for the specified date and time.  N  N  N  N 
TIMEFROMPARTS()  Returns a time value for the specified time and with the specified precision.  N  N  N  N 
@@DATEFIRST  Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 3 for Wednesday, and so on through 7 for Sunday. 
SET DATEFIRST  Sets the first day of the week to a number from 1 through 7  
SET DATEFORMAT  Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. 

For a better understanding, Let's see all the above functions with sample data / query.  

Date and Time Functions - In Detail 

While working with SQL Server's date and time functions we should also know the base datetime of SQL Server. SQL Server's base date is '1900-01-01 00:00:00.000', There is not inbuilt function for this but still we can get SQL Server's base date by query like ,

SELECT CONVERT(DATETIME, 0)   

GETDATE  

GETDATE() is used to get the database system timestamp as a datetime value without the database time zone offset, GETDATE is a nondeterministic function.  Click here to read more from msdn. 

Syntax :-  

GETDATE() 

Example :- 

SELECT  GETDATE() AS 'GETDATE'

Output :-

GETDATE
-----------------------
2013-04-07 22:14:52.820 

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP is used to get database system timestamp as a datetime value without the database time zone offset.This function is the ANSI SQL equivalent to GETDATE. CURRENT_TIMESTAMP is a nondeterministic function. Click here to read more from msdn. 

Syntax :-

CURRENT_TIMESTAMP 

Example :-

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP' 

Output :-

CURRENT_TIMESTAMP
-----------------------
2013-04-07 22:25:00.213 

GETUTCDATE  

GETUTCDATE() is used to get the database system timestamp as a datetime value without the database time zone offset.This value represents the current UTC time. GETUTCDATE is a nondeterministic function. Click here to read more from msdn. 

Syntax :-  

GETUTCDATE()  

Example :-

SELECT GETUTCDATE() AS 'GETUTCDATE' 

Output :-

GETUTCDATE
----------------------
2013-04-07 17:02:43.010  

SYSDATETIME 

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. SYSDATETIME is a nondeterministic function. Click here to read more from msdn. 

Syntax :- 

SYSDATETIME()  

Example :-

SELECT SYSDATETIME() AS 'SYSDATETIME'  

Output :-

SYSDATETIME
--------------------------
2013-04-07 22:40:54.2499987 

SYSDATETIMEOFFSET

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.  SYSDATETIMEOFFSET is a nondeterministic function. Click here to read more from msdn. 

Syntax :-

SYSDATETIMEOFFSET()  

Example :-

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'  

Output :-

SYSDATETIMEOFFSET
----------------------------------
2013-04-07 22:48:58.9317209 +05:30 

SYSUTCDATETIME 

Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time. The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits. SYSUTCDATETIME is a nondeterministic function.  Click here to read more from msdn. 

Syntax :-

SYSUTCDATETIME()

Example :-

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME' 

Output :-

SYSUTCDATETIME
---------------------------
2013-04-07 17:24:48.5077155 

DATENAME

Returns a character string that represents the specified datepart of the specified date. Click here to read more from msdn. 

Syntax :- 

DATENAME ( datepart , date ) 

Sample Code :- 

--Query-------------------------------------------------------Output--------

DECLARE @date DATETIME 
SET @date= '2013-04-07 23:28:42.013' 
 
SELECT @date AS CurrentDate;------------------------------2013-04-07 23:28:42.013

SELECT DATENAME(year,@date) AS 'Year';--------------------2013 
SELECT DATENAME(yy,@date) AS 'Year';----------------------2013 
SELECT DATENAME(yyyy,@date) AS 'Year';--------------------2013 

SELECT DATENAME(quarter,@date) AS 'Quarter';--------------2 
SELECT DATENAME(qq,@date) AS 'Quarter';-------------------2 
SELECT DATENAME(q,@date) AS 'Quarter';--------------------2 

SELECT DATENAME(month,@date) AS 'Month';------------------April 
SELECT DATENAME(mm,@date) AS 'Month';---------------------April
SELECT DATENAME(m,@date) AS 'Month';----------------------April 

SELECT DATENAME(dayofyear,@date) AS 'Dayofyear';----------97 
SELECT DATENAME(dy,@date) AS 'Dayofyear';-----------------97 
SELECT DATENAME(y,@date) AS 'Dayofyear';------------------97 

SELECT DATENAME(day,@date) AS 'Day';----------------------7 
SELECT DATENAME(dd,@date) AS 'Day';-----------------------7 
SELECT DATENAME(d,@date) AS 'Day';------------------------7 

SELECT DATENAME(week,@date) AS 'Week';--------------------15 
SELECT DATENAME(wk,@date) AS 'Week';----------------------15 
SELECT DATENAME(ww,@date) AS 'Week';----------------------15 

SELECT DATENAME(weekday,@date) AS 'Weekday';--------------Sunday 
SELECT DATENAME(dw,@date) AS 'Weekday';-------------------Sunday  
SELECT DATENAME(w,@date) AS 'Weekday';--------------------Sunday  

SELECT DATENAME(hour,@date) AS 'Hour';--------------------23 
SELECT DATENAME(hh,@date) AS 'Hour';----------------------23 

SELECT DATENAME(minute,@date) AS 'Minute';----------------28 
SELECT DATENAME(mi,@date) AS 'Minute';--------------------28 
SELECT DATENAME(n,@date) AS 'Minute';---------------------28 

SELECT DATENAME(second,@date) AS 'Second';----------------42 
SELECT DATENAME(ss,@date) AS 'Second';--------------------42 
SELECT DATENAME(s,@date) AS 'Second';---------------------42 

SELECT DATENAME(millisecond,@date) AS 'Milli Second';-----17 
SELECT DATENAME(ms,@date) AS 'Milli Second';--------------17 

SELECT DATENAME(microsecond,@date) AS 'Micro Second';-----17000 
SELECT DATENAME(mcs,@date) AS 'Micro Second';-------------17000 

SELECT DATENAME(nanosecond,@date) AS 'Nano Second';-------17000000 
SELECT DATENAME(ns,@date) AS 'Nano Second';---------------91000017000000000 

SELECT DATENAME(TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- +05:30 
SELECT DATENAME(tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- +05:30 

SELECT DATENAME(ISO_WEEK,@date) AS 'ISO_WEEK';-------------14
SELECT DATENAME(ISOWK,@date) AS 'ISO_WEEK';----------------14
SELECT DATENAME(ISOWW,@date) AS 'ISO_WEEK';----------------14  

DATEPART

Returns an integer that represents the specified datepart of the specified date. This is very similar to DATENAME. Click here to read more from msdn.  

Syntax :-  

DATEPART ( datepart , date )

Sample Code :-

--Query-------------------------------------------------------Output--------

DECLARE @date DATETIME 
SET @date= '2013-04-07 23:28:42.013' 
 
SELECT @date AS CurrentDate;------------------------------2013-04-07 23:28:42.013

SELECT DATEPART (year,@date) AS 'Year';--------------------2013 
SELECT DATEPART (yy,@date) AS 'Year';----------------------2013 
SELECT DATEPART (yyyy,@date) AS 'Year';--------------------2013 

SELECT DATEPART (quarter,@date) AS 'Quarter';--------------2 
SELECT DATEPART (qq,@date) AS 'Quarter';-------------------2 
SELECT DATEPART (q,@date) AS 'Quarter';--------------------2 

SELECT DATEPART (month,@date) AS 'Month';------------------4 
SELECT DATEPART (mm,@date) AS 'Month';---------------------4
SELECT DATEPART (m,@date) AS 'Month';----------------------4 

SELECT DATEPART (dayofyear,@date) AS 'Dayofyear';----------97 
SELECT DATEPART (dy,@date) AS 'Dayofyear';-----------------97 
SELECT DATEPART (y,@date) AS 'Dayofyear';------------------97 

SELECT DATEPART (day,@date) AS 'Day';----------------------7 
SELECT DATEPART (dd,@date) AS 'Day';-----------------------7 
SELECT DATEPART (d,@date) AS 'Day';------------------------7 

SELECT DATEPART (week,@date) AS 'Week';--------------------15 
SELECT DATEPART (wk,@date) AS 'Week';----------------------15 
SELECT DATEPART (ww,@date) AS 'Week';----------------------15 

SELECT DATEPART (weekday,@date) AS 'Weekday';--------------1 
SELECT DATEPART (dw,@date) AS 'Weekday';-------------------1  
SELECT DATEPART (w,@date) AS 'Weekday';--------------------1  

SELECT DATEPART (hour,@date) AS 'Hour';--------------------23 
SELECT DATEPART (hh,@date) AS 'Hour';----------------------23 

SELECT DATEPART (minute,@date) AS 'Minute';----------------28 
SELECT DATEPART (mi,@date) AS 'Minute';--------------------28 
SELECT DATEPART (n,@date) AS 'Minute';---------------------28 

SELECT DATEPART (second,@date) AS 'Second';----------------42 
SELECT DATEPART (ss,@date) AS 'Second';--------------------42 
SELECT DATEPART (s,@date) AS 'Second';---------------------42 

SELECT DATEPART (millisecond,@date) AS 'Milli Second';-----17 
SELECT DATEPART (ms,@date) AS 'Milli Second';--------------17 

SELECT DATEPART (microsecond,@date) AS 'Micro Second';-----17000 
SELECT DATEPART (mcs,@date) AS 'Micro Second';-------------17000 

SELECT DATEPART (nanosecond,@date) AS 'Nano Second';-------17000000 
SELECT DATEPART (ns,@date) AS 'Nano Second';---------------91000017000000000 

SELECT DATEPART (TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- +05:30 
SELECT DATEPART (tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- +05:30 

SELECT DATEPART (ISO_WEEK,@date) AS 'ISO_WEEK';-------------14
SELECT DATEPART (ISOWK,@date) AS 'ISO_WEEK';----------------14
SELECT DATEPART (ISOWW,@date) AS 'ISO_WEEK';----------------14  

DAY

Returns an integer representing the day (day of the month) of the specified date. DAY returns the same value as DATEPART (day, date).If date contains only a time part, the return value is 1, the base day. Click here to read more from msdn. 

Syntax :-

DAY ( date ) 

Example :-

SELECT DAY ('2013-04-09 11:02:39.880') AS 'DAY1', DAY ('11:02:39.880') AS 'DAY2'   

Output :-

DAY1	DAY2
-----------
9	1 

MONTH

Returns an integer that represents the month of the specified date. MONTH returns the same value as DATEPART (month, date).If date contains only a time part, the return value is 1, the base month.  Click here to read more from msdn. 

Syntax :-

MONTH (date) 

Example :-

SELECT MONTH ('2013-04-09 11:02:39.880') AS 'MONTH1', MONTH ('11:02:39.880') AS 'MONTH2' 

Output :- 

MONTH1	MONTH2
------	------
4	1 

YEAR

Returns an integer that represents the year of the specified date. YEAR returns the same value as DATEPART (year, date). If date only contains a time part, the return value is 1900, the base yearClick here to read more from msdn. 

Syntax :-

YEAR(date) 

Example :-

SELECT YEAR ('2013-04-09 11:02:39.880') AS 'YEAR1', YEAR ('11:02:39.880') AS 'YEAR2', YEAR(0) AS 'YEAR3' 

Output :- 

YEAR1	YEAR2	YEAR3
---------------------
2013	1900	1900  

ISDATE 

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ISDATE returns 0 if the expression is a datetime2 value  or any other value.. 

Syntax :-

ISDATE ( expression ) 

Sample :- 

SELECT ISDATE('2013-04-30 11:29:55.160')  AS 'ISDATE' -- RETURNS 1
SELECT ISDATE(NULL)  AS 'ISDATE' -- RETURNS 0
SELECT ISDATE('SHEMEER')  AS 'ISDATE' -- RETURNS 0 

DATEADD  

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. datepart cannot be User-defined variable or its equivalents. Click here to read more from msdn. 

Syntax :-

DATEADD (datepart , number , date ) 

Example :-

DECLARE @date DATETIME 
 
SET @date= '2013-04-07 23:28:42.013' 
 
SELECT DATEADD(year, 1, @date) AS 'Year', 
       DATEADD(yy, 1, @date)   AS 'Year', 
       DATEADD(yyyy, 1, @date) AS 'Year';           
 
SELECT DATEADD(quarter, 1, @date) AS 'Quarter', 
       DATEADD(qq, 1, @date)      AS 'Quarter', 
       DATEADD(q, 1, @date)       AS 'Quarter'; 
 
SELECT DATEADD(month, 1, @date) AS 'Month', 
       DATEADD(mm, 1, @date)    AS 'Month', 
       DATEADD(m, 1, @date)     AS 'Month'; 
 
SELECT DATEADD(dayofyear, 1, @date) AS 'Dayofyear', 
       DATEADD(dy, 1, @date)        AS 'Dayofyear', 
       DATEADD(y, 1, @date)         AS 'Dayofyear'; 
 
SELECT DATEADD(day, 1, @date) AS 'Day', 
       DATEADD(dd, 1, @date)  AS 'Day', 
       DATEADD(d, 1, @date)   AS 'Day'; 
 
SELECT DATEADD(week, 1, @date) AS 'Week', 
       DATEADD(wk, 1, @date)   AS 'Week', 
       DATEADD(ww, 1, @date)   AS 'Week'; 
 
SELECT DATEADD(weekday, 1, @date) AS 'Weekday', 
       DATEADD(dw, 1, @date)      AS 'Weekday', 
       DATEADD(w, 1, @date)       AS 'Weekday'; 
 
SELECT DATEADD(hour, 1, @date) AS 'Hour', 
       DATEADD(hh, 1, @date)   AS 'Hour'; 
 
SELECT DATEADD(minute, 1, @date) AS 'Minute', 
       DATEADD(mi, 1, @date)     AS 'Minute', 
       DATEADD(n, 1, @date)      AS 'Minute' 
 
SELECT DATEADD(second, 1, @date) AS 'Second', 
       DATEADD(ss, 1, @date)     AS 'Second', 
       DATEADD(s, 1, @date)      AS 'Second'; 
 
SELECT DATEADD(millisecond, 1, @date) AS 'Milli Second', 
       DATEADD(ms, 1, @date)          AS 'Milli Second';  

Output :-

I left this for you :)  

DATEDIFF

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. Click here to read more from msdn. 

Syntax: -

DATEDIFF (datepart,startdate,enddate) 

Example :- 

DECLARE @date1 DATETIME 
DECLARE @date2 DATETIME 
DECLARE @date3 DATETIME 
 
SET @date1= '2012-04-07 20:12:22.013' 
SET @date2= '2014-02-27 22:14:10.013' 
SET @date3= '2013-03-17 23:10:35.013'    

The above code block used for all samples of DATEDIFF,

datepart for finding difference between startdate and enddate in year is year, yy, yyyy. 

SELECT DATEDIFF(year, @date1, @date1) AS 'Year', 
       DATEDIFF(yy, @date1, @date2)   AS 'Year', 
       DATEDIFF(yyyy, @date3, @date2) AS 'Year';           
       
/* Output       
       
Year	Year	Year
--------------------
0	2	1       
*/ 

datepart for finding difference between startdate and enddate in quarter is qq, q, quarter

SELECT DATEDIFF(quarter, @date1, @date1) AS 'Quarter', 
       DATEDIFF(qq, @date1, @date2)   AS 'Quarter', 
       DATEDIFF(q, @date3, @date2) AS 'Quarter';        
        
/* Output          
Quarter Quarter	Quarter
--------------------
0	7	4   
*/ 

datepart for finding difference between startdate and enddate in month is mm, m, month

SELECT DATEDIFF(month, @date1, @date1) AS 'Month', 
       DATEDIFF(mm, @date1, @date2)    AS 'Month', 
       DATEDIFF(m, @date2, @date3)     AS 'Month'; 
       
/* Output
Month	Month	Month
--------------------
0	22	-11
*/ 

datepart for finding difference between startdate and enddate in date of year is dy, y, dateofyear

SELECT DATEDIFF(dayofyear, @date1, @date1) AS 'Dayofyear', 
       DATEDIFF(dy, @date1, @date2)        AS 'Dayofyear', 
       DATEDIFF(y, @date2, @date3)         AS 'Dayofyear'; 
       
/* Output
Dayofyear	Dayofyear	Dayofyear
---------------------------------
0	691	-347       
*/ 

datepart for finding difference between startdate and enddate in day is dd, d, day

SELECT DATEDIFF(day, @date1, @date1) AS 'Day', 
       DATEDIFF(dd, @date1, @date2)  AS 'Day', 
       DATEDIFF(d, @date2, @date3)   AS 'Day'; 
       
/* Output
Day	Day	Day
------------
0	691	-347
*/   

datepart for finding difference between startdate and enddate in week is wk, ww, week

SELECT DATEDIFF(week, @date1, @date1) AS 'Week', 
       DATEDIFF(wk, @date1, @date2)   AS 'Week', 
       DATEDIFF(ww, @date2, @date3)   AS 'Week'; 
       
/* Output
Week	Week	Week
--------------------
0	99	-49       
*/ 

datepart for finding difference between startdate and enddate in weekday is dw, w, weekday

SELECT DATEDIFF(weekday, @date1, @date1) AS 'Weekday', 
       DATEDIFF(dw, @date1, @date2)      AS 'Weekday', 
       DATEDIFF(w, @date2, @date3)       AS 'Weekday'; 
       
/* Output
Weekday	Weekday	Weekday
------------------------
0	691	-347      
*/ 

datepart for finding difference between startdate and enddate in hour is hh, hour

SELECT DATEDIFF(hour, @date1, @date1) AS 'Hour', 
       DATEDIFF(hh, @date2, @date3)   AS 'Hour'; 
 
/* Output
Hour	Hour
------------
0	-8327      
*/  

datepart for finding difference between startdate and enddate in minute is mi, n, minute

SELECT DATEDIFF(minute, @date1, @date1) AS 'Minute', 
       DATEDIFF(mi, @date1, @date2)     AS 'Minute', 
       DATEDIFF(n, @date2, @date3)      AS 'Minute' 
 
/* Output
Minute	Minute	Minute
----------------------
0	995162	-499624      
*/  

datepart for finding difference between startdate and enddate in second is ss, s, second

SELECT DATEDIFF(second, @date1, @date1) AS 'Second', 
       DATEDIFF(ss, @date1, @date2)     AS 'Second', 
       DATEDIFF(s, @date2, @date3)      AS 'Second'; 
 
/* Output
Second	Second	Second
-------------------------
0	59709708	-29977415     
*/ 

datepart for finding difference between startdate and enddate in millisecond is ms, millisecond

SELECT DATEDIFF(millisecond, @date1, @date1) AS 'Milli Second',
      DATEDIFF(ms, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')          AS 'Milli Second'; 
       
/* Output
Milli Second	Milli Second
----------------------------
0	1      
*/  

datepart for finding difference between startdate and enddate in microsecond is mcs, microsecond

SELECT DATEDIFF(microsecond, @date1, @date1) AS 'Micro Second', 
       DATEDIFF(mcs, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000')     AS 'Micro Second'; 
 
/* Output
Micro Second	Micro Second
----------------------------
0	1      
*/  

datepart for finding difference between startdate and enddate in nano second is nanosecond, ns

SELECT DATEDIFF(nanosecond, @date1, @date1) AS 'Nano Second', 
       DATEDIFF(ns, @date1, @date1)         AS 'Nano Second';        
       
/* Output
Nano Second	Nano Second
-----------------------
0	0      
*/   

EOMONTH 

Returns the last day of the month that contains the specified date, with an optional offset. Click here to read more from msdn. 

Syntax :- 

EOMONTH ( start_date [, month_to_add ] )  

Here 'month_to_add' is an Optional integer expression specifying the number of months to add to start_date. 

Example :-

DECLARE @date DATETIME = '2012-04-07'; 
 
SELECT EOMONTH (@date)     AS 'Last Day Of This Month', 
       EOMONTH (@date, 1)  AS 'Last Day Of Next Month', 
       EOMONTH (@date, -1) AS 'Last Day Of Previous Month';  

Output :-

Last Day Of This Month	Last Day Of Next Month	Last Day Of Previous Month	
------------------------------------------------------------------------------
2012-04-31		2012-05-31		2012-03-31 

If 'month_to_add' addition overflows the valid range of dates, then an error is raised. 

SWITCHOFFSET

Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. Click here to read more from msdn. 

Syntax :-

SWITCHOFFSET ( DATETIMEOFFSET, time_zone ) 

Example :-

SELECT SWITCHOFFSET ('2013-04-16 21:15:00.71345 +5:30', '-06:00') AS 'SWITCHOFFSET' 

Output :- 

SWITCHOFFSET
----------------------------------
2013-04-16 09:45:00.7134500 -06:00 

TODATETIMEOFFSET

Returns a datetimeoffset value that is translated from a datetime2 expression. Click here to read more from msdn. 

Syntax :- 

TODATETIMEOFFSET ( expression , time_zone )  

DATEFROMPARTS

Returns a date value for the specified year, month, and dayClick here to read more from msdn. 

Syntax :-  

DATEFROMPARTS ( year, month, day ) 

Example :-

SELECT DATEFROMPARTS ( 2013, 04, 31 ) AS 'DATEFROMPARTS'; 

Output :-

DATEFROMPARTS
------------
2013-04-31 

DATETIME2FROMPARTS 

Returns a datetime2 value for the specified date and time and with the specified precision. Click here to read more from msdn. 

Syntax :-

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Example :-

SELECT DATETIME2FROMPARTS ( 2013, 9, 17, 14, 25, 32, 5, 1 ) AS 'DATETIME2FROMPARTS';  

Output :- 

DATETIME2FROMPARTS
----------------------
2013-09-17 14:25:32.5  

DATETIMEFROMPARTS

Returns a datetime value for the specified date and timeClick here to read more from msdn. 

Syntax :- 

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds ) 

Example :-

SELECT DATETIMEFROMPARTS ( 2013, 04, 31, 22, 55, 56, 0 ) AS 'DATETIMEFROMPARTS';  

Output :- 

DATETIMEFROMPARTS
-----------------------
2013-04-31 22:55:56.000 

DATETIMEOFFSETFROMPARTS 

Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision. Click here to read more from msdn. 

Syntax :- 

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) 

Example :-

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS 'DATETIMEOFFSETFROMPARTS';  

Output :-  

DATETIMEOFFSETFROMPARTS
----------------------------------
2010-12-07 00:00:00.0000000 +00:00 

SMALLDATETIMEFROMPARTS  

Returns a smalldatetime value for the specified date and time. Click here to read more from msdn. 

Syntax :-

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Example :-

SELECT SMALLDATETIMEFROMPARTS ( 2012, 12, 31, 23, 59 ) AS 'SMALLDATETIMEFROMPARTS' 

Output :- 

SMALLDATETIMEFROMPARTS
----------------------
2013-01-01 00:00:00 

TIMEFROMPARTS 

Returns a time value for the specified time and with the specified precision. Click here to read more from msdn. 

Syntax :-

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Example :-

SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS 'TIMEFROMPARTS'; 

Output :- 

TIMEFROMPARTS
----------------
23:59:59.0000000 

@@DATEFIRST  

Returns the current value, for a session, of SET DATEFIRSTClick here to read more from msdn. 

Syntax :-  

@@DATEFIRST  

Example :- 

SELECT GETDATE() AS 'GETDATE',@@DATEFIRST AS '@@DATEFIRST' 

Output :-

GETDATE	    @@DATEFIRST
-------------------------
2013-04-11 12:15:36.390	7 

SET DATEFIRST 

Sets the first day of the week to a number from 1 through 7. Click here to read more from msdn. 

Syntax :-

SET DATEFIRST { number | @number_var }  

Where 'number | @number_var' indicates the first day of the week as an integer. number 1 is Monday, 2 is Tuesday ... and  7 is Sunday. To see the current setting of SET DATEFIRST, use the @@DATEFIRST function. 

Example :-

SET DATEFIRST 3;
-- Because Wednesday is now considered the first day of the week, 
-- DATEPART now shows that 2013-04-15 (a Monday) is the sixth day of the  
-- week. The following DATEPART function should return a value of 6. 
SELECT CAST('2013-04-15' AS DATETIME) AS 'DATE', 
       DATEPART(dw, '2013-04-15')     AS 'DayOfWeek', 
       @@DATEFIRST                    AS '@@DATEFIRST'; 
 
GO 

Output :- 

DATE	DayOfWeek	@@DATEFIRST
----------------------------------------------------
2013-04-15 00:00:00.000	6	3  

SET DATEFORMAT  

Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. Click here to read more from msdn.  

Syntax :-

SET DATEFORMAT { format | @format_var } 

Where 'format | @format_var' Is the order of the date parts. Valid parameters are mdy, dmy, ymd, ydm, myd, and dym. The DATEFORMAT ydm is not supported for date, datetime2 and datetimeoffset data types. SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE

Example :- 

-- Set date format to day/month/year.
SET DATEFORMAT dmy;
GO
DECLARE @date DATE = '15/04/2013';
SELECT @date AS '@date';
GO  

Output :- 

@date
----------
2013-04-15 

Functions that can affect the output of date functions 

The functions mentioned in this section are not a date or time function. However, this function setting can affect the output of date and time functions. 

@@LANGUAGE 

Returns the name of the language currently being used. Click here to read more from msdn. 

Syntax :-  

@@LANGUAGE 

Example :-

SELECT @@LANGUAGE AS '@@LANGUAGE' 

Output :- 

@@LANGUAGE
----------
us_english 

SET LANGUAGE  

Specifies the language environment for the session. The session language determines the datetime formats and system messages. Click here to read more from msdn. 

Syntax :-

SET LANGUAGE { [ N ] 'language' | @language_var }  

SET LANGUAGE implicitly sets the setting of SET DATEFORMAT. 

Sample :- 

DECLARE @Today DATETIME
SET @Today = '11/19/2013'
 
SET LANGUAGE Arabic
SELECT DATENAME(month, @Today) AS 'Month'
 
/* Output
Month
------------
Thou Alqadah
*/
 
SET LANGUAGE us_english
SELECT DATENAME(month, @Today) AS 'Month' 
GO
 
/* Output
Month
------------
November
*/ 

sp_helplanguage  

Reports information about a particular alternative language or about all languages. Click here to read more from msdn. 

Syntax :- 

sp_helplanguage [ [ @language = ] 'language' ]  

Example :- 

sp_helplanguage Arabic; 

Output :- 

References  

I have specified all reference as a 'read more' link in all items, apart from that the main references are given below, 

Summary  

In this article I have explained Date and Time Data Types and Functions of SQL Server (2000 and above)  from a beginner's perspective. If I have missed anything or need any change in definition then please let me know. I hope you have enjoyed this article and got some value addition to your knowledge.  

You might be interested in the below articles.   

I have put my time and efforts on all of my articles, Please don't forget to mark your votes, suggestions and feedback to improve the quality of this and upcoming articles. Thanks for reading.  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Shemeer NS
Software Developer (Senior)
India India
Technology Specialist | CodeProject MVP | Visual Studio Gallery Contributor | Author | Geek | Netizen | Husband | ChessPlayer
 
Most of my articles are listed on top 5 of the respective 'Best articles of the month' and some of my articles are published on ASP.NET WebSite's Article of the Day section.
 
Check my contributions in Visual Studio Gallery and Code Project
 
Technical Blog: http://www.shemeerns.com
Facebook: http://facebook.com/shemeernsblog
Twitter : http://twitter.com/shemeerns
Google+ : http://google.com/+Shemeernsblog
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionUsed your article... PinmemberSander Rossel5-Jan-14 21:05 
QuestionWow... PinmemberSander Rossel18-Nov-13 10:11 
QuestionMessage Automatically Removed PinmemberMember 1037057930-Oct-13 5:50 
QuestionMessage Automatically Removed PinmemberMember 1037057930-Oct-13 5:38 
GeneralUseful PinprofessionalAmir Farid5-Oct-13 20:39 
QuestionWell done PinmemberAndrej Milas12-Sep-13 9:17 
GeneralMy vote of 5 PinmemberAnchita Dubey4-Sep-13 9:46 
GeneralMy vote of 5 PinmemberAadhar Joshi12-Jul-13 2:45 
GeneralExcellent Article! --- minor issue of 31 for April PinmemberBGW15-May-13 17:39 
GeneralRe: Excellent Article! --- minor issue of 31 for April PinmvpShemeer NS15-May-13 19:47 
GeneralMy vote of 5 PinmemberMihai MOGA10-May-13 18:32 
GeneralRe: My vote of 5 PinmvpShemeer NS13-May-13 8:10 
GeneralMy vote of 5 PinmemberKamarajub3-May-13 1:58 
GeneralRe: My vote of 5 PinmvpShemeer NS13-May-13 8:07 
GeneralMy vote of 5 PinmemberGuyThiebaut2-May-13 22:57 
GeneralRe: My vote of 5 PinmvpShemeer NS13-May-13 8:06 
GeneralAnother Vote of 5 PinmemberMember 828805230-Apr-13 13:30 
GeneralRe: Another Vote of 5 PinmvpShemeer NS13-May-13 8:06 
GeneralMy vote of 4 Pinmemberhebsiboy25-Apr-13 5:19 
GeneralRe: My vote of 4 PinmvpShemeer NS13-May-13 8:06 
GeneralMy vote of 5 PinmemberPrasad Khandekar24-Apr-13 21:05 
GeneralRe: My vote of 5 PinmvpShemeer NS13-May-13 8:05 
GeneralMy vote of 5 PinprofessionalAmol_B24-Apr-13 20:45 
GeneralRe: My vote of 5 PinmvpShemeer NS13-May-13 8:05 
Thanks Amol
Shemeer NS

GeneralMy vote of 5 PinmemberVitorHugoGarcia22-Apr-13 4:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 3 May 2013
Article Copyright 2013 by Shemeer NS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid