Friday, 10 January 2014

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

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

http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server

 

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  N   Y   Y   Y  
date  N  N   Y   Y   Y  
smalldatetime  Y  Y   Y   Y   Y  
datetime  Y  Y   Y   Y   Y  
datetime2  N  N   Y   Y   Y  
datetimeoffset  N  N   Y   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.
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 


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. 
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.
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. 
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.
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.  
Date and Time Functions - SQL Server 2000 and 2005 
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. 
Date and Time Functions - SQL Server 2008 and 2008 R2 
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. 
Date and Time Functions - SQL Server 2012
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.  Y  Y  Y  Y  Y 
CURRENT_TIMESTAMP  Returns the current date and time. This function is the ANSI SQL equivalent to GETDATE.  Y Y 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.  Y  Y  Y  Y  Y 
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   Y  Y  Y 
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   Y  Y  Y 
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   Y  Y  Y 
DATENAME()  Returns a character string representing the specified datepart of the specified date.  Y  Y  Y  Y  Y 
DATEPART()  Returns an integer representing the specified datepart of the specified date.  Y  Y  Y  Y  Y 
DAY()  Returns an integer representing the daYdatepart of the specified date.  Y  Y  Y  Y  Y 
MONTH()  Returns an integer that represents the month part of a specified date.  Y  Y  Y  Y  Y 
YEAR()  Returns an integer that represents the year part of a specified date.  Y  Y  Y  Y  Y 
DATEADD()  Returns a new datetime value based on adding an interval to the specified date.  Y  Y  Y  Y  Y 
DATEDIFF()  Returns the number of date and time boundaries crossed between two specified dates.  Y  Y  Y  Y  Y 
ISDATE()  Determines whether an input expression is a valid date.  Y  Y  Y  Y  Y 
EMONTH()  Returns the last day of the month that contains the specified date, with an optional offset.  N N N N  Y 
SWITCHOFFSET()  Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.  N N  Y  Y  Y 
TODATETIMEOFFSET()  Returns a datetimeoffset value that is translated from a datetime2 expression.  N N  Y  Y  Y 
DATEFROMPARTS()  Returns a date value for the specified year, month, and day.  N N N N  Y 
DATETIME2FROMPARTS()  Returns a datetime2 value for the specified date and time and with the specified precision.  N N N N  Y 
DATETIMEFROMPARTS()  Returns a datetime value for the specified date and time.  N N N N  Y 
DATETIMEOFFSETFROMPARTS()  Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.  N N N N  Y 
SMALLDATETIMEFROMPARTS()  Returns a smalldatetime value for the specified date and time.  N N N N  Y 
TIMEFROMPARTS()  Returns a time value for the specified time and with the specified precision.  N N N N  Y 
@@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.  Y  Y  Y  Y  Y 
SET DATEFIRST  Sets the first day of the week to a number from 1 through 7   Y  Y  Y  Y  Y 
SET DATEFORMAT  Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.  Y  Y  Y  Y  Y 
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.
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.

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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Syntax :-
TODATETIMEOFFSET ( expression , time_zone )

DATEFROMPARTS

Returns a date value for the specified year, month, and day.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Syntax :-
sp_helplanguage [ [ @language = ] 'language' ]
Example :-
sp_helplanguage Arabic;
Output :- 
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)