Tutorials
All about the sql function

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 at 10:58 am
14
0
1
live preview
enter atleast 10 characters
WARNING: You mentioned %MENTIONS%, but they cannot see this message and will not be notified
Saving...
Saved
With selected deselect posts show selected posts
All posts under this topic will be deleted ?
Pending draft ... Click to resume editing
Discard draft