SQL server Date Time Function

 

Datetime functions allow manipulating columns with DATETIME/SMALLDATETIME data types. SQL server DateTime Function:

GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE Functions are Nondeterministic function. Both functions returns the current date and time. GETDATE returns current system date and time of the computer where SQL Server is running.

GETUTCDATE returns current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

DATEADD Functions

DATEADD function is Deterministic function. DATEADD Function adds a certain interval of time to the specified date and time value.

Syntax: DATEADD (datepart , number, date )

DATEADD returns a new date time value based on adding an interval to the specified date and time value.

DATEDIFF Function

DATEDIFF function is Deterministic function. DATEDIFF () gives the difference between the two date values.

Syntax: DATEDIFF ( datepart , startdate , enddate )

DATEDIFF returns number of date and time boundaries crossed between two specified dates. In DATEDIFF function start date is subtracted from end date. If start date is later than end date, a negative value is returned.

DATEPART Function

To retrieve any part of date and time use DATEPART function.

Syntax: DATEPART ( datepart , date )

DATEPART function takes two arguments 1)part of the date that you want to retrieve and 2)date itself. The DATEPART function returns an integer that represents date part of specified date.

DATEPART	Output
SELECT DATEPART(year, '2009-02-13 18:35:06.523')	2009
SELECT DATEPART(quarter, '2009-02-13 18:35:06.523')	1
SELECT DATEPART(month, '2009-02-13 18:35:06.523')	2
SELECT DATEPART(dayofyear, '2009-02-13 18:35:06.523')	44
SELECT DATEPART(day, '2009-02-13 18:35:06.523')	13
SELECT DATEPART(week, '2009-02-13 18:35:06.523')	7
SELECT DATEPART(weekday, '2009-02-13 18:35:06.523')	6
SELECT DATEPART(hour, '2009-02-13 18:35:06.523')	18
SELECT DATEPART(minute, '2009-02-13 18:35:06.523')	35
SELECT DATEPART(second, '2009-02-1 18:35:06.523')	6
SELECT DATEPART(millisecond, '2009-02-1 18:35:06.523')	523

DATENAME Function

DATENAME Function returns a character string that represents date part of the specified date.

Syntax: DATENAME ( datepart , date )

DATENAME function takes two arguments same as DATEPART function 1) part of the date that you want to retrieve and 2) date itself.

If you want the name of month
SELECT DATENAME (month, '2009-02-13 18:35:06.523')  => Output : February

If you want the name of week day
SELECT DATENAME (weekday, '2009-02-13 18:35:06.523')  => Output : Friday

DAY, MONTH, and YEAR Functions

All of this DAY, MONTH, and YEAR functions takes a single date value as a argument. Each of this function returns an integer that represents respective portions of the date.

SELECT   DAY('2009-02-1 18:35:06.523') as 'Day', MONTH('2009-02-1 18:35:06.523') as 'Month', YEAR('2009-02-1 18:35:06.523') as 'Year'

Each of this functions equivalent to DATEPART function. Like DAY is equivalent to DATEPART (dd, date), MONTH is equivalent to DATEPART (mm, date) and YEAR is equivalent to DATEPART (yy, date).