SQL Server Functions - Date Functions

Below are the SQL server DATE Functions.

CURRENT_TIMESTAMP

- The function returns the current datetime of the system in a yyyy-dd-MM hh:mm:ss Format.

Syntax

CURRENT_TIMESTAMP

Example

SELECT CURRENT_TIMESTAMP as current_datetime

Output

current_datetime
2018-09-11 21:10:38.157

DATEADD

- This function adds the date/time to the specified date and returns the value.

Syntax

DATEADD(interval,value,date)

Parameters

interval- the date or time to return. this can be yyyy-year,mm- month,DD-Day and many more.
value- numeric value. the number of intervals.
date- the date in which intervals to be added.

Example

SELECT DATEADD(DD,3,GETDATE()) as newdatetime

Output

newdatetime
2018-09-14 21:40:00.600

DATEDIFF

-This function return the difference between two dates as specified by the interval. Syntax

DATEDIFF(interval,date1,date2)

Parameters

interval- the date or time to return. this can be yyyy-year,mm- month,DD-Day and many more.
date1- first date value.
date2- second date value.

Example

SELECT DATEDIFF(DD,GETDATE()-4,getdate()) as datedifference

Output

datedifference
4

Example 2

SELECT DATEDIFF(Q,'2018-01-01 00:00:00','2018-04-01 00:00:00') as quarterdiff

Output

quarterdiff
1

DATENAME

- This function return the part of the specified date as string value. Syntax

DATENAME(interval,date)

Example

SELECT DATENAME(YY,GETDATE()) as yearofdate

Output

yearofdate
2018

Example 2

SELECT DATENAME(MM,GETDATE()) as monthofdate

Output

monthofdate
September

DATEPART

- This function return the part of the date in integer format. Syntax

DATEPART(interval,value)

Example

SELECT DATEPART(MM,GETDATE()) as monthofdate

Output

monthofdate
9

DAY

- This function returns the day (from 1- 31) from the specified date. Syntax

DAY(date)

Example

SELECT DAY(GETDATE()) as dayofthemonth

Output

dayofthemonth 11

GETDATE()

- This function returns the current date and time of the system. Syntax

GETDATE()

Example

SELECT GETDATE() as currentdate

Output

currentdate
2018-09-12 20:00:01.607

GETUTCDATE()

- This function returns the current date and time of the system. Syntax

GETDATE()

Example

SELECT GETUTCDATE() as currentutcdate

Output

currentutcdate
2018-09-12 14:32:57.773

ISDATE(date)

- This functions return 1 if expression is valid date otherwise 0. Syntax

ISDATE(date)

Example

SELECT ISDATE('2018-09-12 14:32:57.773') as isvaliddate

Output

isvaliddate
1

MONTH(date)

- This function return the month from the provided date. Syntax

MONTH(date)

Example

SELECT MONTH('2018-09-12') as monthindate

Output

monthindate
9

YEAR(date)

- This function return the Year from the provided date. Syntax

YEAR(date)

Example

SELECT YEAR('2018-09-12') as yearindate

Output

yearindate
2018