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

• Download Scripts.zip - 8.7 KB 

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  

• Introduction 
• Background 
• Date and Time Data Types - Quick Reference 
• Date and Time Data Types - In Detail 
• time 
• date 
• smalldatetime  
• datetime 
• datetime2
• datetimeoffset
• Date and Time Functions - Quick Reference. 
• Date and Time Functions - SQL Server 2000 and 2005
• Date and Time Functions - SQL Server 2008 and 2008 R2 
• Date and Time Functions - SQL Server 2012
• Date and Time Functions - SQL Server 
• Date and Time Functions - In Detail  
• GETDATE 
• CURRENT_TIMESTAMP 
• GETUTCDATE 
• SYSDATETIME 
• SYSDATETIMEOFFSET 
• SYSUTCDATETIME
• DATENAME 
• DATEPART
• DAY 
• MONTH
• YEAR
• DATEADD 
• DATEDIFF
• ISDATE 
• EOMONTH
• SWITCHOFFSET 
• TODATETIMEOFFSET
• DATEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEFROMPARTS 
• DATETIMEOFFSETFROMPARTS
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS 
• @@DATEFIRST 
• SET DATEFIRST 
• SET DATEFORMAT
• Functions that can affect the output of date functions 
• @@LANGUAGE
• SET LANGUAGE
• sp_helplanguage
• Functions that helps to convert date and time values to and from string literals and other date and time formats. 
• Interesting Date and Time Questions  
• References 
• Summary  

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. 

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.    

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 year. Click 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 day. Click 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 time. Click 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 DATEFIRST. Click 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, 

• MSDN- Date and Time Data Types and Functions (Transact-SQL)

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.   

• Functions that helps to convert date and time values to and from string literals and other date and time formats.  
• Some Interesting Date and Time Questions.    
• String Data Types and Functions - SQL Server 
• .NET Framework Cultures with Culture Specific Formats and Mapping with SQL Server Language 

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.