SQL DATE and TIME Functions

Summary -

In this topic, we described about the DATE and TIME Functions with detailed example.

DATE Functions that return current DATE and TIME are listed as follows –

Function	Description	Syntax
CURRENT_TIMESTAMP	It returns the current date and time without offset time zone. CURRENT_TIMESTAMP has no arguments. It is equal to GETDATE( ). We can use CURRENT_TIMESTAMP as default date.	CURRENT_TIMESTAMP
GETUTCDATE()	It returns the current UTC timestamp date and time in result set. GETUTCDATE() has no arguments.	GETUTCDATE()
GETDATE()	It returns the current date and time of the current operating system in result set. GETDATE() has no arguments.	GETDATE()
SYSDATETIME()	It returns the current date and time of current operating system with more seconds precision than GETDATE(). SYSDATETIME() has no arguments.	SYSDATETIME()
SYSUTCDATETIME()	It returns the current UTC timestamp date and time in result set. SYSDATETIME() has no arguments. SYSDATETIME() has more seconds precision than the GETUTCDATE().	SYSDATETIME()
SYSDATETIMEOFFSET()	It returns the current date and time with time zone offset in result set. SYSDATETIMEOFFSET() has no arguments.	SYSDATETIMEOFFSET()

DATE Functions that return current DATE and TIME with examples are listed as follows –

Function

Example

Output

CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP AS A;

Now we can get current date and time like as follows

A
2020-09-15 09:50:46.913

GETUTCDATE()

SELECT GETUTCDATE() AS A;

Now we can get current UTC timestamp date and time like as follows -

A
2020-09-15 04:46:02.860

GETDATE()

SELECT GETDATE() AS A;

Now we can get current date and time like as follows -

A
2020-09-15 10:25:00.540

SYSDATETIME()

SELECT SYSDATETIME() AS A;

Now we can get current date and time like as follows

A
2020-09-15 10:27:32.7961078

SYSUTCDATETIME()

SELECT SYSUTCDATETIME() AS A;

Now we can get current UTC timestamp date and time like as follows -

A
2020-09-15 05:12:17.0373633

SYSDATETIMEOFFSET()

SELECT SYSDATETIMEOFFSET() AS A;

Now we can get current date and time with time zone offset like as follows -

A
2020-09-15 10:58:42.1236341 +05:30

DATE Functions that return part of TIME and DATE are listed as follows -

Function

Description

Syntax

DATENAME()

It returns character string of the specified part of the date in result set.
DATENAME has two arguments as input_date like the date which we want to get its part , date_part like format as year, month, date. The format types that are used in DATENAME to get different parts of the date are listed as follows-

date_part	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
Millisecond	ms
microsecond	mcs
nanosecond	ns
tz	TZoffset
ISO_WEEK	isowk, isoww

DATENAME(date_part, input_date)

DATEPART()

It returns the integer part of specified part of the date. DATEPART has two arguments as input_date like the date which we want to get its part , date_part like format as year, month, date. DATEPART is similar to DATENAME but the return type is different.

DATEPART(date_part, input_date)

DAY()

DAY extracts day from specified DATE. It returns an integer value from 1 to 31. DAY has one argument only which has DATE type.

DAY(input_date)

MONTH()

MONTH extracts month from specified date. It returns integer value from 1 to 12. MONTH has one argument only which has DATE type.

MONTH(input_date)

YEAR()

YEAR extracts year from specified date. It returns integer value in result set. YEAR has one argument only which has DATE type.

YEAR(input_date)

DATE Functions that return part of TIME and DATE with examples are explained as follows -

Function

Example

Output

DATENAME

EX 1 – DECLARE @dt DATETIME2= '2020-09-15 11:54:21.5058411 +05:30' ;
SELECT 'year,yyy,yy' required_part,
DATENAME(year, @dt) result
UNION
SELECT 'quarter, qq, q',
DATENAME(quarter, @dt)
UNION
SELECT 'month, mm, m',DATENAME(month, @dt)
UNION
SELECT 'dayofyear, dy, y', DATENAME(dayofyear, @dt)
UNION
SELECT 'day, dd, d', DATENAME(day, @dt)
UNION
SELECT 'week, wk, ww', DATENAME(week, @dt)
UNION
SELECT 'weekday, dw, w',DATENAME(weekday, @dt)
UNION
SELECT 'hour, hh' date_part, DATENAME(hour, @dt)
UNION
SELECT 'minute, mi,n', DATENAME(minute, @dt) UNION SELECT 'second, ss, s', DATENAME(second, @dt) UNION SELECT 'millisecond, ms', DATENAME(millisecond, @dt) UNION SELECT 'microsecond, mcs', DATENAME(microsecond, @dt) UNION SELECT 'nanosecond, ns', DATENAME(nanosecond, @dt) UNION SELECT 'TZoffset, tz', DATENAME(tz, @dt)
UNION
SELECT 'ISO_WEEK, ISOWK, ISOWW', DATENAME(ISO_WEEK, @dt) ;
EX 2 – SELECT DATENAME(YEAR, @dt) + '1' ;

Now we can get the required part of the date with specified format like as follows -

required_part	result
day, dd, d	15
dayofyear, dy, y	259
hour, hh	11
ISO_WEEK, ISOWK, ISOWW	38
microsecond, mcs	505841
millisecond, ms	505
minute, mi,n	54
month, mm, m	September
nanosecond, ns	505841100
quarter, qq, q	3
second, ss, s	21
TZoffset, tz	+00:00
week, wk, ww	38
weekday, dw, w	Tuesday
year,yyy,yy	2020

By executing example 2 we can get output like as shown in below -

A
2021

DATEPART()

EX 1 – DECLARE @dt DATETIME2= '2020-09-15 11:54:21.5058411 +05:30' ;
SELECT 'year,yyy,yy' required_part,
DATEPART(year, @dt) result
EX 2 – SELECT DATENAME(YEAR, @dt) + '1' ;

Now we can get the required part of the date with specified format like as follows -

required_part	result
year,yyy,yy	2020

By executing example 2 we can get output like as shown in below –

A
2021

DAY()

SELECT DAY('2020-09-15') AS A;

Now we can get output like as shown in below –

A
15

MONTH()

SELECT MONTH('2020-09-15') AS A;

Now we can get output like as shown in below –

A
9

YEAR()

SELECT YEAR('2020-09-15') AS A;

Now we can get output like as shown in below –

A
2020

DATE Functions that return difference between two dates are listed as below -

Function

Description

Syntax

DATEDIFF()

It returns an integer value in result set i.e. difference between two dates. DATEDIFF has three arguments date_part, first_date, second_date. We can compare two dates as er our specified parts by mentioning in date_part that are listed as follows –

date_part	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
Millisecond	ms
microsecond	mcs
nanosecond	ns

DATEDIFF(date_part, first_date, second_date)

DATE Functions that return difference between two dates are explained with examples as shown in below -

Function

Example

Output

DATEDIFF()

SELECT DATEDIFF(year, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(quarter, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(month, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(datofyear, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(day, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(week, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(hour, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(minute, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(second, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

SELECT DATEDIFF(millisecond, '2020-09-15 15:06:57.3898211 +05:30 ', '2020-09-15 10:58:42.1236341 +05:30 ' ) AS A ;

Now we can get output like as shown in below –

A
0

A
0

A
0

A
-11

A
-11

A
-2

A
-269

A
-16088

A
-965295

A
-965295266

DATE Functions that are used in SQL to modify date are listed as below –

Function

Description

Syntax

DATEADD( )

It returns the modified data value by adding integer value to the specified part of the DATE. DATEADD has three arguments like date_part, value, input_date. We can specify which part we want to add to the date in date_part like as follows –

date_part	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
Millisecond	ms
microsecond	mcs
nanosecond	ns

DATEADD(date_part, value, input_date)

EOMONTH()

It returns the last day of month of the specified date. EOMONTH has two arguments input_date, offset. The offset value is optional, and it is an integer value added to the month value of specified date.

EOMONTH(input_date, offset_value)

SWITCHOFFSET()

It returns the modified date value by replacing time zone of specified date value with required time zone value. SWITCHOFFSET has two arguments as expression, new_time_zone. expression value is should be in DATETIMEOFFSET type. time zone is in the format of [+/-]TZH:TZM. The range of hour is from +14 to -14.

SWITCHOFFSET(expression, new_time_zone)

TODATETIMEOFFSET()

It returns modified value by modifying datetime value with time zone in result set. It has two arguments like expression and time_zone. time zone is in the format of [+/-]TZH:TZM. The range of hour is from +14 to -14.time zone should be an integer value also.

TODATETIMEOFFSET(expression, time_zone)

DATE Functions that are used in SQL to modify date are explained with examples as below -

Function

Example

Output

DATEADD()

SELECT DATEADD(year, 1, '2020-09-04 10:58:42') modifieddateasyear ;

SELECT DATEADD(quarter, 1, '2020-09-04 10:58:42') modifieddateasquarter ;

SELECT DATEADD(month, 1, '2020-09-04 10:58:42') modifieddateasmonth ;

SELECT DATEADD(dayofyear, 1, '2020-09-04 10:58:42') modifieddateasdayofyear;

SELECT DATEADD(day, 1, '2020-09-04 10:58:42') modifieddateasday ;

SELECT DATEADD(week, 1, '2020-09-04 10:58:42') modifieddateasweek ;

SELECT DATEADD(hour, 1, '2020-09-04 10:58:42') modifieddateashour ;

SELECT DATEADD(minute, 1, '2020-09-04 10:58:42') modifieddateasminute ;

SELECT DATEADD(day, 1, '2020-09-04 10:58:42') modifieddateassecond ;

SELECT DATEADD(day, 1, '2020-09-04 10:58:42') modifieddateasmillisecond ;

Now we can get output like as shown in below –

modifieddateasyear
2021-09-04 10:58:42.000

modifieddateasquarter
2020-12-04 10:58:42.000

modifieddateasmonth
2020-10-04 10:58:42.000

modifieddateasdayofyear
2020-09-05 10:58:42.000

modifieddateasday
2020-09-05 10:58:42.000

modifieddateasweek
2020-09-11 10:58:42.000

modifieddateashour
2020-09-04 11:58:42.000

modifieddateasminute
2020-09-04 10:59:42.000

modifieddateassecond
2020-09-04 10:58:43.000

modifieddateasmillisecond
2020-09-04 10:58:42.000

EOMONTH()

SELECT EOMONTH('2020-09-15') AS A, EOMONTH(GETDATE()) AS B, EOMONTH('2020-09-15', 2) AS C;

Now we can get output like as shown in below –

A	B	C
2020-09-30	2020-09-30	2020-11-30

SWITCHOFFSET()

SELECT SWITCHOFFSET('2020-09-15 18:02:03.2267931 +05:30', '+06:45' ) AS A;

Now we can get output like as shown in below –

A
2020-09-15 19:17:03.2267931 +06:45

TODATETIMEOFFSET()

SELECT TODATETIMEOFFSET('2020-09-15 18:02:03', '+05:30') AS A;

Now we can get output like as shown in below –

A
2020-09-15 18:02:03.0000000 +05:30

DATE Functions that are creating date and time from the specified values are listed as follows -

Function	Description	Syntax
DATEFROMPARTS()	It returns valid DATE from specified values. It has three arguments like year, month, day. The range of year vary from 0 to 9999, The range of month vary from 0 to 12 The range of day vary from 0 to 31. If we give null values to any those arguments in result set we get NULL value. If we give invalid values in arguments we get output like as Cannot construct data type date, some of the arguments have values which are not valid.	DATEFROMPARTS(year, month, day)
DATETIME2FROMPARTS()	It returns valid date with time in result set. It has eight arguments like year, month, day, hour, minute, second, fraction, precision. Except fraction, precision all those are have specified with in the range only. fraction refers the fractional seconds value. Precision refers how many digits in the result set we want in result set.	DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fraction, precision)
DATETIMEOFFSETFROMPARTS()	It returns valid date with time and time zone in result set. . It has ten arguments like year, month, day, hour, minute, second, fraction, precision hour_offset, minute_offset. Except fraction, precision all those are have specified within the range only. fraction refers the fractional seconds value. Precision refers how many digits in the result set we want in result set.	DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fraction, hour_offset, minute_offset, precision)
TIMEFROMPARTS()	It returns full time value from the specified values.It has five arguments like hour, minute, second, fraction, precision. Precision will not be NULL, If it is null function returns an error in result set.	TIMEFROMPARTS(hour, minute, seconds, fraction, precision)

DATE Functions that are creating date and time from the specified values are explained as shown in below –

Function

Example

Output

DATEFROMPARTS()

SELECT DATEFROMPARTS('2020','09','15') AS A, DATEFROMPARTS('0001',NULL,'15') AS B;

SELECT DATEFROMPARTS ('2020','02','30') AS C;

Now we can get output like as shown in below –

A	B
2020-09-15	NULL

Now we can get output like as follows –
Cannot construct data type date, some of the arguments have values which are not valid.

DATETIME2FROMPARTS()

SELECT DATETIME2FROMPARTS('2020','09','15', '22', '55', '45', 2589,4);

Now we can get output like as shown in below –

A
2020-09-15 22:55:45.2589

DATETIMEOFFSETFROMPARTS()

SELECT DATETIMEOFFSETFROMPARTS(2020, 09, 15, 19,11, 30,1666996,8,05,7) AS A;

Now we can get output like as shown in below –

A
2020-09-15 19:11:30.1666996 +08:05

TIMEFROMPARTS()

SELECT TIMEFROMPARTS(10,25,43,652,4) AS A;

Now we can get output like as shown in below –

A
10:25:43.0652

DATE Functions that are validating date and time from the specified values are listed as shown in below –

Function	Description	Syntax
ISDATE()	It checks whether the specified date and time are valid or not. It returns 1, if it is valid or returns 0,if it is not valid.	ISDATE(expression)

DATE Functions that are validating date and time from the specified values are explained as shown in below -

Function

Example

Output

ISDATE()

SELECT ISDATE('20200915') AS A;

Now we can get output like as shown in below -

A
1