Read about Time/Date function in SQL

Learn about all date function which is used to manipulate in the SQL server with the help of Date function in SQL for example - CURRENT_TIMESTAMP function in SQL, DATEADD, DATENAME.

Read about Time/Date function in SQL

CURRENT_TIMESTAMP function in SQL : -

SQL gives a function to the user which helps to return the current date and time in the SQL server and that function is known as CURRENT_TIMESTAMP function.

Syntax : - 

CURRENT_TIMESTAMP

This function gives date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'.

See the example : - 

SELECT CURRENT_TIMESTAMP;
Result: '2014-05-01 14:19:55.918'

DATEADD function in SQL : -

DATEADD function in SQL helps to give a date after adding or subtracting the interval from the date.

dateadd function in SQL

Syntax:- 

DATEADD( interval, number, date )

Example : -

SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'

SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'
SELECT DATEADD(year, 1, '2014/05/29');
Result: '2015-05-29 00:00:00.000'
SELECT DATEADD(year, -1, '2014/03/23');
Result: '2013-03-23 00:00:00.000'

SELECT DATEADD(month, 1, '2014/09/18');
Result: '2014-09-18 00:00:00.000'

SELECT DATEADD(month, -1, '2014/04/28');
Result: '2014-03-28 00:00:00.000'

SELECT DATEADD(day, 1, '2014/04/28');
Result: '2014-04-29 00:00:00.000'

SELECT DATEADD(day, -1, '2014/04/28');
Result: '2014-04-27 00:00:00.000'

DATEDIFF function in SQL : -

DATEDIFF function tells about the between two date values, based on the interval specified.

datediff function in SQL

Syntax:- 

DATEDIFF( interval, date1, date2 )

The interval used to calculate the difference between date1 and date2.

Example : - 

SELECT DATEDIFF(year, '2012/01/18', '2014/01/28');
Result: 2

SELECT DATEDIFF(yyyy, '2010/04/28', '2012/04/28');
Result: 2

SELECT DATEDIFF(yy, '2010/04/28', '2012/04/28');
Result: 2

SELECT DATEDIFF(month, '2014/01/01', '2014/04/28');
Result: 3

SELECT DATEDIFF(day, '2014/01/01', '2014/04/28');
Result: 117

SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 2

SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 165

DATENAME in SQL : -

DATENAME function in SQL gives the specified part of the of a given date, as a string value

Syntax:- 

DATENAME( interval, date )

it will return the result in the string.

Example : - 

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2012/04/28');
Result: '2012'

SELECT DATENAME(yy, '2010/04/28');
Result: '2010'

SELECT DATENAME(month, '2014/01/28');
Result: 'Jan'

SELECT DATENAME(day, '2014/04/19');
Result: '19'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'

DATEPART in SQL : -

DATEPART function in SQL gives the specified part of the of a given date, as an integer value.

Syntax:- 

DATEPART( interval, date )

it will return the result in the integer.

Example : - 

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2012/04/28');
Result: '2012'

SELECT DATENAME(yy, '2010/04/28');
Result: '2010'

SELECT DATENAME(month, '2014/01/28');
Result: '1'

SELECT DATENAME(day, '2014/04/19');
Result: '19'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'

DATEPART in SQL : -

DAY function tells about the day of the month (between 1 to 31) given a date value.

Syntax:- 

DAY( date_value )

Example : - 

SELECT DAY('2014/04/22');
Result: 22

SELECT DAY('2014/03/23 10:05');
Result: 23

SELECT DAY('2014/04/11 10:05:18.621');
Result: 11

GETDATE SQL function : -

GATEDATE function in SQL gives you the current date and time just like the CURRENT_TIMESTAMP function.

Syntax:- 

GETDATE ( )

Example : - 

SELECT GETDATE();
Result: '2014-04-28 18:17:28.160'

GETUTCDATE SQL function : -

GETUTCDATE function for SQL server returns the current UTC date and time.

Syntax:- 

GETUTCDATE ( )

Example : - 

SELECT GETUTCDATE();
Result: '2014-04-29 00:27:58.657' 

MONTH Function in SQL : -

MONTH function tells about the MONTH of the month (between 1 to 12) given a date value.

Syntax:- 

MONTH( date_value )

Example : - 

SELECT MONTH('2014/02/28');
Result: 2

SELECT MONTH('2014/01/31 10:05');
Result: 1

SELECT MONTH('2014/10/01 10:05:18.621');
Result: 10

MONTH Function in SQL : -

MONTH function tells about the four-digit year (in number) given a date value.

Syntax:- 

YEAR( date_value )

Example : - 

SELECT YEAR('2012/04/28');
Result: 2012

SELECT YEAR('2009/03/31 10:05');
Result: 2009

SELECT YEAR('2011/12/01 10:05:18.621');
Result: 2011

Read More