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