DATEADD
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD ( datepart , number, date )
datepart
Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.
Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
select dateadd(day,2,getdate())
as Date
TodAY IS 29 AUGUST
Date
----
select dateadd(month,2,getdate()) as Date
Date
----
2008-10-29 16:36:44.523
2008-08-31 16:35:19.477
Similarly Year,min,hours etc.
CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Syntax
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
expression
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.
data_type
Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Without century (yy) With century (yyyy)
Standard
Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101----- USA---- mm/dd/yy
2 102--- ANSI -----yy.mm.dd
3 103 ----British/French -----dd/mm/yy
4 104 -----German----- dd.mm.yy
5 105 ------Italian---- dd-mm-yy
6 106 ---- dd mon yy
7 107 - ----Mon dd, yy
8 108 ----- hh:mm:ss
- 9 or 109 (*) ----- Default + milliseconds---- mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110----- USA -----mm-dd-yy
11 111 -----JAPAN------ yy/mm/dd
12 112 -----ISO -----yymmdd
- 13 or 113----- (*) Europe default + milliseconds ----dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120---- (*) ODBC canonical-------- yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ----- ODBC canonical (with milliseconds)------- yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601------ yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 130* Hijri**** -------dd mon yyyy hh:mi:ss:mmmAM
- 131* Hijri**** dd/mm/yy hh:mi:ss:mmmAM
--
DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
Examples
This example determines the difference in days between the current date and the publication date for titles in the pubs database.
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
SELECT DATENAME(month, getdate()) AS 'Month Name'
Here is the result set:
Month Name
------------------------------
February
SELECT DATENAME(year, getdate()) AS 'Year'
Year
-----
2008
SELECT DATENAME(day, getdate()) AS 'Day'
Day
----
29
DATEPART
Returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO
Here is the result set:
Month Number
------------
2
This example assumes the date May 29.
SELECT DATEPART(month, GETDATE())
GO
Here is the result set:
-----------
5
(1 row(s) affected)
In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
Here is the result set:
----- ------ ------
1 1 1900
Returns a new datetime value based on adding an interval to the specified date.
Syntax
DATEADD ( datepart , number, date )
datepart
Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.
Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
select dateadd(day,2,getdate())
as Date
TodAY IS 29 AUGUST
Date
----
select dateadd(month,2,getdate()) as Date
Date
----
2008-10-29 16:36:44.523
2008-08-31 16:35:19.477
Similarly Year,min,hours etc.
CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.
Syntax
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
expression
Is any valid Microsoft® SQL Server™ expression. For more information, see Expressions.
data_type
Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Without century (yy) With century (yyyy)
Standard
Input/Output**
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101----- USA---- mm/dd/yy
2 102--- ANSI -----yy.mm.dd
3 103 ----British/French -----dd/mm/yy
4 104 -----German----- dd.mm.yy
5 105 ------Italian---- dd-mm-yy
6 106 ---- dd mon yy
7 107 - ----Mon dd, yy
8 108 ----- hh:mm:ss
- 9 or 109 (*) ----- Default + milliseconds---- mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110----- USA -----mm-dd-yy
11 111 -----JAPAN------ yy/mm/dd
12 112 -----ISO -----yymmdd
- 13 or 113----- (*) Europe default + milliseconds ----dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120---- (*) ODBC canonical-------- yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ----- ODBC canonical (with milliseconds)------- yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601------ yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 130* Hijri**** -------dd mon yyyy hh:mi:ss:mmmAM
- 131* Hijri**** dd/mm/yy hh:mi:ss:mmmAM
--
DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.
Syntax
DATEDIFF ( datepart , startdate , enddate )
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
Examples
This example determines the difference in days between the current date and the publication date for titles in the pubs database.
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
SELECT DATENAME(month, getdate()) AS 'Month Name'
Here is the result set:
Month Name
------------------------------
February
SELECT DATENAME(year, getdate()) AS 'Year'
Year
-----
2008
SELECT DATENAME(day, getdate()) AS 'Day'
Day
----
29
DATEPART
Returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO
Here is the result set:
Month Number
------------
2
This example assumes the date May 29.
SELECT DATEPART(month, GETDATE())
GO
Here is the result set:
-----------
5
(1 row(s) affected)
In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
Here is the result set:
----- ------ ------
1 1 1900
Comments