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.

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.

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.

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

````