Date Functions
Sql Server Tutorial
| datepart |
Abbreviations |
| year |
yy, yyyy |
| quarter |
qq, q |
| month |
mm, m |
| dayofyear |
dy, y |
| day |
dd, d |
| week |
wk, ww |
| weekday |
dw, w |
| hour |
hh |
| minute |
mi, n |
| second |
ss, s |
| millisecond |
ms |
| microsecond |
mcs |
| nanosecond |
ns |
DATEADD
| DATEADD: |
Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. |
| Syntax: |
DATEADD (datepart , number, date ) |
| Arguments: |
datepart: See chart at top of page
number: An expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid. Decimal fractions are truncated, not rounded.
date: An expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. Date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime. |
| Return Value: |
Non-String Argument: Return data type is the data type of the date argument.
String Argument: datetime |
| Expression |
Results |
| select DATEADD(year,1,'2009-03-06') |
2010-03-06 00:00:00.000 |
| select DATEADD(month,1,'2009-03-06') |
2009-04-06 00:00:00.000 |
| select DATEADD(day,7,'2009-03-06') |
2009-03-13 00:00:00.000 |
| select DATEADD(month, 6, SYSDATETIME()) |
6 months from current date |
DATEDIFF
| DATEDIFF: |
Returns the count between startdate and enddate |
| Syntax: |
DATEDIFF(datepart, startdate, enddate) |
| Arguments: |
datepart: See chart at top of page
startdate: expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset
enddate: expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset |
| Expression |
Results |
| SELECT DATEDIFF(year, '2009-05-15', '2006-02-07') |
-3 |
| SELECT DATEDIFF(hour, '2009/02/12 23:59:59.9999999', '2009/02/13 00:00:00.0000000') |
1 |
DATENAME
| DATENAME: |
Returns a character string that represents the specified datepart of the specified date |
| Syntax: |
DATENAME(datepart, date) |
| Arguments: |
expression: An expression of any type.
date: An expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value |
| Expression |
Results |
| SELECT DATENAME(year, '2009/02/12') |
2009 |
| SELECT DATENAME(month, '2009/02/12') |
February |
| SELECT DATENAME(day, '2009/02/12') |
12 |
DAY, MONTH, YEAR
| DATENAME: |
Returns a character string that represents the specified datepart of the specified date |
| Syntax: |
DAY(date)
MONTH(date)
YEAR(date) |
| Arguments: |
expression: An expression of any type.
date: An expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value |
| Expression |
Results |
| select DAY('2009/02/12') |
12 |
| select MONTH('2009/02/12') |
2 |
| select YEAR('2009/02/12') |
2009 |
GETDATE, CURRENT_TIMESTAMP
| GETDATE: |
Returns current date and time |
| Syntax: |
GETDATE()
CURRENT_TIMESTAMP |
| Arguments: |
none |
| Expression |
Results |
| select GETDATE() |
current date and time |
| select CURRENT_TIMESTAMP |
2 |
GETUTCDATE
| GETUTCDATE: |
Returns the current UTC (Coordinated Universal Time) time, also referred to as the Greenwich Mean Time. |
| Syntax: |
GETUTCDATE() |
| Arguments: |
none |
| Expression |
Results |
| select GETUTCDATE() |
UTC date and time |
|