MS-SQL Date Functions

The following lists the most important date functions in MS-SQL Server:

•    GETDATE()
•    DATEPART()
•    DATEADD()
•    DATEDIFF()
•    CONVERT()

The following type of date format:

• TIMESTAMP - a unique number
• DATETIME -  YYYY-MM-DD HH:MI:SS
• DATE - YYYY-MM-DD
• SMALLDATETIME - YYYY-MM-DD HH:MI:SS

GETDATE()

  Returns the current date and time

Syntax:

select GETDATE();

Output:

2016-12-10 15:52:29.093

DATEPART()

Returns a single part of a date/time

Syntax:

select DATEPART(MONTH,getdate()) as MONTH
select DATEPART(DAY,getdate()) as DAY
select DATEPART(DAYOFYEAR,getdate()) as DAYOFYEAR
select DATEPART(WEEK,getdate()) as WEEK
select DATEPART(WEEKDAY,getdate()) as WEEKDAY
select DATEPART(ISO_WEEK,getdate()) as ISO_WEEK
select DATEPART(ISOWK,getdate()) as ISOWK
select DATEPART(ISOWW,getdate()) as ISOWW
select DATEPART(MICROSECOND,getdate()) as MICROSECOND
select DATEPART(MILLISECOND,getdate()) as MILLISECOND
select DATEPART(MM,getdate()) as MM

Output:

CONVERT()

Displays date/time data in different formats

Syntax:

CONVERT(data_type(length),expression,style)

Example:

select CONVERT(VARCHAR(19),GETDATE())
select CONVERT(VARCHAR(10),GETDATE(),10)
select CONVERT(VARCHAR(10),GETDATE(),101)
select CONVERT(VARCHAR(11),GETDATE(),102)
select CONVERT(VARCHAR(11),GETDATE(),103)
select CONVERT(VARCHAR(24),GETDATE(),104)
select CONVERT(VARCHAR(10),GETDATE(),105)
select CONVERT(VARCHAR(11),GETDATE(),106)
select CONVERT(VARCHAR(11),GETDATE(),107)
select CONVERT(VARCHAR(24),GETDATE(),108)
select CONVERT(VARCHAR(10),GETDATE(),110)
select CONVERT(VARCHAR(11),GETDATE(),111)
select CONVERT(VARCHAR(11),GETDATE(),112)
select CONVERT(VARCHAR(24),GETDATE(),113)
select CONVERT(VARCHAR(10),GETDATE(),114)
select CONVERT(VARCHAR(24),GETDATE(),121)

Output:

Dec 10 2016  4:25PM
12-10-16
12/10/2016
2016.12.10
10/12/2016
10.12.2016
10-12-2016
10 Dec 2016
Dec 10, 201
16:25:06
12-10-2016
2016/12/10
20161210
10 Dec 2016 16:25:06:993
16:25:06:9
2016-12-10 16:25:06.993

Value (without century)	Value (with century)	Explanation
0	100	mon dd yyyy hh:miAM/PM (Default)
1	101	mm/dd/yyyy (US standard)
2	102	yy.mm.dd (ANSI standard)
3	103	dd/mm/yy (British/French standard)
4	104	dd.mm.yy (German standard)
5	105	dd-mm-yy (Italian standard)
6	106	dd mon yy
7	107	Mon dd, yy
8	108	hh:mi:ss
9	109	mon dd yyyy hh:mi:ss:mmmAM/PM
10	110	mm-dd-yy (USA standard)
11	111	yy/mm/dd (Japan standard)
12	112	yymmdd (ISO standard)
13	113	dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock)
14	114	hh:mi:ss:mmm (24 hour clock)
20	120	yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21	121	yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical - 24 hour clock)
	126	yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
	127	yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
	130	dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
	131	dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)

DATEADD()
Adds or subtracts a specified time interval from a date

Syntax:

DATEADD(datepart,number,date)

Parameters
datepart
    The time/date datepart that you wish to add. It can be one of the following values:

Value (any one of)	Explanation
year, yyyy, yy	Year datepart
quarter, qq, q	Quarter datepart
month, mm, m	Month datepart
dayofyear	Day of year datepart
day, dy, y	Day datepart
week, dw, w	Week datepart
weekday, ww, wk	Weekday datepart
hour, hh	Hour datepart
minute, mi, n	Minute datepart
second, ss, s	Second datepart
millisecond, ms	Millisecond datepart

number
The number of dateparts that you wish to add.

date
The date to which the datepart should be added.

Example:

SELECT DATEADD(year, 1, '1995/03/20');
 SELECT DATEADD(quarter, 1, '1995/03/20');
 SELECT DATEADD(month, 1, '1995/03/20');
 SELECT DATEADD(dayofyear, 1, '1995/03/20');
 SELECT DATEADD(day, 1, '1995/03/20');
 SELECT DATEADD(week, 1, '1995/03/20');
 SELECT DATEADD(weekday, 1, '1995/03/20');
 SELECT DATEADD(hour, 1, '1995/03/20');
 SELECT DATEADD(minute, 1, '1995/03/20');
 SELECT DATEADD(second, 1, '1995/03/20');
 SELECT DATEADD(millisecond, 1, '1995/03/20');
 SELECT DATEADD(hour, -1, '1995/03/20');
 SELECT DATEADD(minute, -1, '1995/03/20');
 SELECT DATEADD(second, -1, '1995/03/20');

Output:

1996-03-20 00:00:00.000
1995-06-20 00:00:00.000
1995-04-20 00:00:00.000
1995-03-21 00:00:00.000
1995-03-21 00:00:00.000
1995-03-27 00:00:00.000
1995-03-21 00:00:00.000
1995-03-20 01:00:00.000
1995-03-20 00:01:00.000
1995-03-20 00:00:01.000
1995-03-20 00:00:00.000
1995-03-19 23:00:00.000
1995-03-19 23:59:00.000
1995-03-19 23:59:59.000

DATEDIFF()

Returns the time between two dates

Syntax:

DATEDIFF(datepart,startdate,enddate)

Value (any one of)	Explanation
year, yyyy, yy	Year datepart
quarter, qq, q	Quarter datepart
month, mm, m	Month datepart
dayofyear	Day of year datepart
day, dy, y	Day datepart
week, dw, w	Week datepart
weekday, ww, wk	Weekday datepart
hour, hh	Hour datepart
minute, mi, n	Minute datepart
second, ss, s	Second datepart
millisecond, ms	Millisecond datepart

Example:

SELECT DATEDIFF(year,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(quarter,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(month,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(dayofyear,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(day, '1995/03/20','2017/03/20');
 SELECT DATEDIFF(week,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(weekday,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(hour,'1995/03/20','2017/03/20');
 SELECT DATEDIFF(minute,'1995/03/20','2017/03/20');

Output:

22 year
88 quarter
264 month
8036 dayofyear
8036 day
1148 week
8036 weekday
192864 hour
11571840 minute
694310400 minute