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
````
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
````
````
edited Jun 7 '20 at 10:58 am