SQL-Server Date and Time Functions

In this tutorial, I am going to explain you SQL-Server Date and Time functions with example. Below is the list of most commonly used Date and Time function.

  • GETDATE
  • SYSDATETIME
  • CURRENT_TIMESTAMP
  • GETUTCDATE
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME
  • ISDATE
  • DAY
  • MONTH
  • YEAR
  • DATENAME
  • DATEPART
  • DATEADD
  • DATEDIFF

Below is explanation for each function.

GETDATE Function

Syntax:
GETDATE( )
Description:
It is used to return the current database system timestamp as a datetime value without the database time zone offset.This value is derived from the operating system of the computer on which the instance of SQL Server is running.
Example:

SELECT GETDATE() --> [2017-11-12 11:17:02.807]

--With CONVERT
SELECT CONVERT(DATE, GETDATE())                --> [2017-11-12]
SELECT CONVERT(DATE,'2017-11-12 11:17:02.807') --> [2017-11-12]
SELECT CONVERT(DATE,'2017-11-12 11:17:02.807') --> [2017-11-12]
SELECT CONVERT(TIME, GETDATE())                --> [11:18:05.2470000]
SELECT CONVERT(TIME,'2017-11-12 11:17:02.807') --> [11:17:02.8070000]
SELECT CONVERT(TIME,'2017-11-12 00:00:00.000') --> [00:00:00.0000000]

NOTE:
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

SYSDATETIME Function

Syntax:
SYSDATETIME( )
Description:
It is similar to GETDATE function that returns date and time, but it has more precision and its return type is datetime2.
Example:

SELECT SYSDATETIME() --> [2017-11-06 15:45:33.0236202]

--With CONVERT
SELECT CONVERT(DATE, SYSDATETIME())                --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 15:47:43.5074056') --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 00:00:00.0000000') --> [2017-11-06]
SELECT CONVERT(TIME, SYSDATETIME())                --> [15:33:51.4170000]
SELECT CONVERT(TIME,'2017-11-06 15:47:43.5074056') --> [15:47:43.5074056]
SELECT CONVERT(TIME,'2017-11-06 00:00:00.0000000') --> [00:00:00.0000000]

CURRENT_TIMESTAMP Function

Syntax:
CURRENT_TIMESTAMP
Description:
It is similar to GETDATE function.
Example:

SELECT CURRENT_TIMESTAMP --> [2017-11-06 15:28:08.033]

--With CONVERT
SELECT CONVERT(DATE, CURRENT_TIMESTAMP)        --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 15:28:08.033') --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 00:00:00.000') --> [2017-11-06]
SELECT CONVERT(TIME, CURRENT_TIMESTAMP)        --> [15:33:51.4170000]
SELECT CONVERT(TIME,'2017-11-06 15:28:08.033') --> [15:28:08.0330000]
SELECT CONVERT(TIME,'2017-11-06 00:00:00.000') --> [00:00:00.0000000]

GETUTCDATE Function

Syntax:
GETUTCDATE()
Description:
It returns the current database system timestamp as a [datetime] value.
The database time zone offset is not included.
This value represents the current UTC time (Coordinated Universal Time) or (GMT)Greenwich Mean Time.
This value is derived from the operating system of the computer on which the instance of SQL Server is running.
Example:

SELECT GETUTCDATE() --> [2017-11-06 10:32:01.583]

--With CONVERT
SELECT CONVERT(DATE, GETUTCDATE())             --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 10:32:01.583') --> [2017-11-06]
SELECT CONVERT(DATE,'2017-11-06 10:32:01.583') --> [2017-11-06]
SELECT CONVERT(TIME, GETUTCDATE())             --> [10:32:32.3930000]
SELECT CONVERT(TIME,'2017-11-06 10:32:01.583') --> [10:32:01.5830000]
SELECT CONVERT(TIME,'2017-11-06 00:00:00.000') --> [00:00:00.0000000]

SYSDATETIMEOFFSET Function

Syntax:
SYSDATETIMEOFFSET( )
Description:
It 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.
Example:

SELECT SYSDATETIMEOFFSET() --> [2017-11-06 18:12:20.2277109 +05:30]

SYSUTCDATETIME Function

Syntax:
SYSUTCDATETIME( )
Description:
It 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.
Example:

SELECT SYSUTCDATETIME() --> [2017-11-06 12:45:43.4203159]

ISDATE Function

Syntax:
ISDATE( expression )
Description:
It returns 1 if supplied value is valid date, time, or datetime, otherwise it returns 0.
Example:

SELECT ISDATE(GETDATE())                 --> 1
SELECT ISDATE('2017-11-10 14:10:19.880') --> 1
SELECT ISDATE('02-28-2017')              --> 1
SELECT ISDATE('02-29-2017')              --> 0
SELECT ISDATE('2017-02-28')              --> 1
SELECT ISDATE('2017/02/28')              --> 1
SELECT ISDATE('2017/02/29')              --> 0
SELECT ISDATE('Hello')                   --> 0
SELECT ISDATE('04/2017/15')              --> 1

--> You can check different date format using below query
SELECT * FROM  sys.syslanguages

--> To find current language use below query
SELECT * FROM  sys.syslanguages Where  name = @@Language

Example:
--> Use these sessions settings.
SET LANGUAGE us_english 
SET DATEFORMAT mdy 
--> Expression in mdy dateformat
SELECT ISDATE('04/15/2008') --> [1]
--> Expression in mdy dateformat
SELECT ISDATE('04-15-2008') --> [1]  
--> Expression in mdy dateformat
SELECT ISDATE('04.15.2008') --> [1]  
--> Expression in myd  dateformat
SELECT ISDATE('04/2008/15') --> [1] 

SET DATEFORMAT mdy 
SELECT ISDATE('15/04/2008') --> [0] 
SET DATEFORMAT mdy 
SELECT ISDATE('15/2008/04') --> [0]
SET DATEFORMAT mdy 
SELECT ISDATE('2008/15/04') --> [0] 
SET DATEFORMAT mdy 
SELECT ISDATE('2008/04/15') --> [1] 

SET DATEFORMAT dmy 
SELECT ISDATE('15/04/2008') --> [1] 
SET DATEFORMAT dym 
SELECT ISDATE('15/2008/04') --> [1] 
SET DATEFORMAT ydm 
SELECT ISDATE('2008/15/04') --> [1] 
SET DATEFORMAT ymd 
SELECT ISDATE('2008/04/15') --> [1] 

SET LANGUAGE English 
SELECT ISDATE('15/04/2008') --> [0] 
SET LANGUAGE Hungarian 
SELECT ISDATE('15/2008/04') --> [0] 
SET LANGUAGE Swedish 
SELECT ISDATE('2008/15/04') --> [0]
SET LANGUAGE Italian 
SELECT ISDATE('2008/04/15') --> [1] 

--> Return to these sessions settings.
SET LANGUAGE us_english 
SET DATEFORMAT mdy

DAY Function

Syntax:
DAY( date )
Description:
It returns day number of month of supplied date.
Example:

SELECT DAY('2017-11-10 14:39:46.377') --> 10
SELECT GETDATE() --> 2017-11-10 14:39:46.377
SELECT DAY(GETDATE()) --> 10
SELECT DAY('2017-11-10 14:10:19.880') --> 10
SELECT DAY('02-28-2017') --> 28
SELECT DAY('2017-02-28') --> 28
SELECT DAY('2017/02/28') --> 28

MONTH Function

Syntax:
MONTH( date )
Description:
It returns month number of the year of supplied date.
Example:

SELECT MONTH('2017-11-10 14:39:46.377') --> 11
SELECT MONTH(GETDATE()) --> 11
SELECT MONTH('2017-11-10 14:10:19.880') --> 11
SELECT MONTH('02-28-2017') --> 2
SELECT MONTH('2017-02-28') --> 2
SELECT MONTH('2017/02/28') --> 2

YEAR Function

Syntax:
YEAR( date )
Description:
It returns year number of supplied date.
Example:

SELECT YEAR('2017-11-10 14:39:46.377') --> 2017
SELECT YEAR(GETDATE()) --> 2017
SELECT YEAR('2015-11-10 14:10:19.880') --> 2015
SELECT YEAR('02-28-2017') --> 2017
SELECT YEAR('2016-02-28') --> 2016
SELECT YEAR('2018/02/28') --> 2018

DATENAME Function

Syntax:
DATENAME( datepart , date )
Description:
It returns string that represent datepart of supplied date.
It takes two input parameter, first is DatePart that we want and second is actual supplied date.

datepartAbbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz
ISO_WEEK ISOWK, ISOWW
Example
SELECT DATENAME(DAY, GETDATE()) --> 11
SELECT DATENAME(DAY, '2017-11-11 22:16:30.550')--> 11
SELECT DATENAME(MONTH, '2017-11-11 22:16:30.550') --> November
SELECT DATENAME(WEEK, '2017-11-11 22:16:30.550') --> 45
SELECT DATENAME(WEEKDAY, '2017-11-11 22:16:30.550') --> Saturday
SELECT DATENAME(YEAR,'02-28-2017') --> 2017
SELECT DATENAME(WEEK,'02-28-2017') --> 9
SELECT DATENAME(YEAR,'2018/01/10') --> 2018
SELECT DATENAME(WEEK,'2018/01/10') --> 2
SELECT DATENAME(MS, '2017-11-11 22:16:30.550') --> 550
SELECT DATENAME(MILLISECOND, '2017-11-11 22:16:30.550') --> 550

DATEPART Function

Syntax:
DATEPART( datepart , date )
Description:
It is similar DATENAME function where it returns integer that represent date part of supplied date.
It takes two input parameter, first is DatePart that we want and second is actual supplied date.

datepartAbbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz
ISO_WEEK ISOWK, ISOWW
Example
SELECT DATEPART(DAY, GETDATE()) -->  11
SELECT DATEPART(DAY, '2017-11-11 22:16:30.550')--> 11
SELECT DATEPART(MONTH, '2017-11-11 22:16:30.550') --> 11
SELECT DATEPART(WEEK, '2017-11-11 22:16:30.550') --> 45
SELECT DATEPART(WEEKDAY, '2017-11-11 22:16:30.550') -->7
SELECT DATEPART(WEEKDAY, '2017-11-12 22:16:30.550') -->1
SELECT DATEPART(YEAR,'02-28-2017') --> 2017
SELECT DATEPART(WEEK,'02-28-2017') --> 9
SELECT DATEPART(YEAR,'2018/01/10') --> 2018
SELECT DATEPART(WEEK,'2018/01/10') --> 2
SELECT DATEPART(MS, '2017-11-11 22:16:30.550') --> 550
SELECT DATEPART(MILLISECOND, '2017-11-11 22:16:30.550') --> 550

DATEADD Function

Syntax:
DATEADD(datepart , number , date )
Description:
It returns datetime after adding integer to specfied datepart of supplied date.

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
SELECT DATEADD(MONTH,15 ,'2017-11-11 22:16:30.550') --> 2019-02-11 22:16:30.550
SELECT DATEADD(DAY, 15, '2017-11-11 22:16:30.550')  --> 2017-11-26 22:16:30.550
SELECT DATEADD(WEEK,2,'02-28-2017')                 --> 2017-03-14 00:00:00.000
SELECT DATEADD(DAY, -15, '2017-11-11 22:16:30.550') --> 2017-10-27 22:16:30.550

DATEDIFF Function

Syntax:
DATEDIFF( datepart , startdate , enddate )
Description:
It returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
SELECT DATEDIFF(DAY,'2018-01-10','2018-02-10')    --> 32
SELECT DATEDIFF(MONTH, '02-28-2017','02-28-2018') --> 12
SELECT DATEDIFF(DAY, '02-28-2017','2017-03-28')   --> 28


Share this

Related Posts

Previous
Next Post »