SQL Server Date function
The following table lists the most important built-in date functions in SQL Server:
function |
Description |
GETDATE () |
Returns the current date and time |
DATEPART () |
Returns a separate part of the date/time |
DATEADD () |
Add or subtract a specified time interval in a date |
DATEDIFF () |
Returns a time between two dates |
CONVERT () |
Display Date/time in a different format |
The (1), GETDATE () function returns the current date and time from SQL Server.
GrammarGETDATE ()
Example
Here is the SELECT statement:
SELECT GETDATE () as Currentdatetime
The results are as follows:
Currentdatetime |
2008-11-11 12:45:34.243 |
(2),
The DATEPART () function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.
GrammarDATEPART (Datepart,date)
The date parameter is a valid day expression. The datepart parameter can be the following value:
DatePart |
Abbreviations |
Years |
YY, yyyy |
Quarter |
QQ, Q |
Month |
MM, M |
The day of the year |
Dy, y |
Day |
DD, D |
Week |
WK, WW |
Week |
DW, W |
Hours |
hh |
Minutes |
MI, n |
Seconds |
SS, S |
Milliseconds |
Ms |
Subtle |
MCs |
Na-Sec |
Ns |
Example
Suppose we have the following "Orders" table:
OrderId |
ProductName |
OrderDate |
1 |
Jarlsberg Cheese |
2008-11-11 13:23:44.657 |
Here is the SELECT statement:
SELECT DATEPART (yyyy,orderdate) as OrderYear, DATEPART (mm,orderdate) as OrderMonth, DATEPART (dd,orderdate) as Orderday, From Orders WHERE orderid=1
The results are as follows:
OrderYear |
OrderMonth |
Orderday |
2008 |
11 |
11 |
(3),
The DATEADD () function adds or subtracts a specified time interval from a date.
GrammarDATEADD (Datepart,number,date)
The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative.
The datepart parameter can be the following value:
DatePart |
Abbreviations |
Years |
YY, yyyy |
Quarter |
QQ, Q |
Month |
MM, M |
The day of the year |
Dy, y |
Day |
DD, D |
Week |
WK, WW |
Week |
DW, W |
Hours |
hh |
Minutes |
MI, n |
Seconds |
SS, S |
Milliseconds |
Ms |
Subtle |
MCs |
Na-Sec |
Ns |
Example
Suppose we have the following "Orders" table:
OrderId |
ProductName |
OrderDate |
1 |
Jarlsberg Cheese |
2008-11-11 13:23:44.657 |
Now we want to add 45 days to "OrderDate" so we can find the payment date.
We use the following SELECT statement:
SELECT Orderid,dateadd (day,45,orderdate) as orderpaydate from Orders
Results:
OrderId |
orderpaydate |
1 |
2008-12-26 13:23:44.657 |
(4),
The DATEDIFF () function returns the number of days between two dates.
GrammarDATEDIFF (Datepart,startdate,enddate)
The StartDate and EndDate parameters are valid date expressions. The datepart parameter can be the following value:
DatePart |
Abbreviations |
Years |
YY, yyyy |
Quarter |
QQ, Q |
Month |
MM, M |
The day of the year |
Dy, y |
Day |
DD, D |
Week |
WK, WW |
Week |
DW, W |
Hours |
hh |
Minutes |
MI, n |
Seconds |
SS, S |
Milliseconds |
Ms |
Subtle |
MCs |
Na-Sec |
Ns |
Example
Now we want to get a number of days between two dates.
We use the following SELECT statement:
SELECT DATEDIFF (Day, ' 2008-06-05 ', ' 2008-08-05 ') as Diffdate
Results:
Example
Now we want to get two days between dates (note that the second date is earlier than the first date and the result will be a negative number).
We use the following SELECT statement:
SELECT DATEDIFF (Day, ' 2008-08-05 ', ' 2008-06-05 ') as Diffdate
Results:
(5),
The CONVERT () function is a general function that converts a date to a new data type.
The CONVERT () function can display date/time data in different formats.
Grammar convert (
data_type (length),
expression,
style)
value |
Description |
Data_type (length) |
Specifies the target data type (with an optional length). |
Expression |
Specifies the value that needs to be converted. |
Style |
Specifies the date/time of the output format. |
The following table shows the style values that are converted to character data by datetime or smalldatetime:
value (Century yy) |
value (century yyyy) |
Input/Output |
Standard |
- |
0 or 100 |
Mon dd yyyy hh:miam (or PM) |
Default |
1 |
101 |
Mm/dd/yy |
USA |
2 |
102 |
Yy.mm.dd |
Ansi |
3 |
103 |
Dd/mm/yy |
British/french |
4 |
104 |
Dd.mm.yy |
German |
5 |
105 |
Dd-mm-yy |
Italian |
6 |
106 |
DD Mon yy |
|
7 |
107 |
Mon DD, yy |
|
8 |
108 |
Hh:mm:ss |
|
- |
9 or 109 |
Mon dd yyyy hh:mi:ss:mmmAM (or PM) |
Default+millisec |
10 |
110 |
Mm-dd-yy |
USA |
11 |
111 |
Yy/mm/dd |
Japan |
12 |
112 |
Yymmdd |
Iso |
- |
113 or |
DD Mon yyyy hh:mi:ss:mmm (24h) |
|
14 |
114 |
Hh:mi:ss:mmm (24h) |
|
- |
120 or |
Yyyy-mm-dd Hh:mi:ss (24h) |
|
- |
or 121 |
Yyyy-mm-dd hh:mi:ss.mmm (24h) |
|
- |
126 |
Yyyy-mm-ddthh:mi:ss.mmm (no spaces) |
ISO8601 |
- |
130 |
DD Mon yyyy hh:mi:ss:mmmAM |
Hijiri |
- |
131 |
Dd/mm/yy Hh:mi:ss:mmmAM |
Hijiri |
Example
The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to get the current date/time:
CONVERT (varchar), GETDATE ()) CONVERT (varchar (), GETDATE (), () convert (varchar), GETDATE (), () Convert ( varchar (one), GETDATE (), 6) CONVERT (varchar (one), GETDATE (), 106) CONVERT (varchar), GETDATE (), 113)
The results are as follows:
Nov 11:45 PM 11-04-11 11-04-2011, Geneva 2011 11:45:34:243
SQL Date () function