November 12th 2017

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(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_TIMESTAMPDescription: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(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 DATEFORMATmdy

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.

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
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.

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
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

The Ultimate Guide to Cloud Gaming: D…
best projectors for home
The Advantages Of Playing Online Casi…
Understanding the Software and Apps o…
The Ultimate Guide to Landing Freelan…

This post first appeared on ASPArticles, please read the originial post: here

People also like

The Ultimate Guide to Cloud Gaming: Discover the Best Services

best projectors for home

The Advantages Of Playing Online Casino Games

Understanding the Software and Apps of the Samsung Galaxy Tab S8 tablet

The Ultimate Guide to Landing Freelance Writing Gigs

SQL-Server Date and Time Functions

GETDATE Function

SYSDATETIME Function

CURRENT_TIMESTAMP Function

GETUTCDATE Function

SYSDATETIMEOFFSET Function

SYSUTCDATETIME Function

ISDATE Function

DAY Function

MONTH Function

YEAR Function

DATENAME Function

DATEPART Function

DATEADD Function

Related Articles

SQL-Server Date and Time Functions

GETDATE Function

SYSDATETIME Function

CURRENT_TIMESTAMP Function

GETUTCDATE Function

SYSDATETIMEOFFSET Function

SYSUTCDATETIME Function

ISDATE Function

DAY Function

MONTH Function

YEAR Function

DATENAME Function

DATEPART Function

DATEADD Function

Related Articles

Share the post

Subscribe to Asparticles

Thank you for your subscription