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).
Related Articles: