Dateadd
Returns a new datetime value based on a specified date.
Syntax
Dateadd (datepart, number, date)
Parameters
Datepart
Is a parameter that specifies which part of the date should be returned to the new value. The following table lists the date and abbreviation recognized by Microsoft & reg; SQL Server & #8482.
Abbreviation of date
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
Number
Is used to increase the value of datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and 1.75 for number, date is increased by 1.
Date
Is an expression that returns the datetime, smalldatetime, or date format string. For more information about the specified date, see datetime and smalldatetime.
If you only specify the last two digits of the year, the number of the last two digits smaller than or equal to the value of the "two-digit year deadline" configuration option is in the same century as the end year. The last two-digit number greater than the value of this option is in the century before the end of the century. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049,2050 as 1950. To avoid blurring, use a four-digit year.
Return type
Return datetime, but if the date parameter is smalldatetime, return smalldatetime.
Example
This example prints a list of the title time structures in the pubs database. This time structure indicates the current release date plus 21 days.
Use pubs
Go
Select dateadd (Day, 21, pubdate) as timeframe
From titles
Go
The following is the result set:
Timeframe
---------------------------
Jul 3 1991 AM
Jun 30 1991 AM
Jul 21 1991 AM
Jul 13 1991 AM
Jun 30 1991 AM
Jul 9 1991 AM
Mar 14 1997 pm
Jul 21 1991 AM
Jul 3 1994 AM
Mar 14 1997 pm
Nov 11 1991 AM
Jul 6 1991 AM
Oct 26 1991 AM
Jul 3 1991 AM
Jul 3 1991 AM
Nov 11 1991 AM
Jul 3 1991 AM
Jul 3 1991 AM
(18 row (s) affected)
Source: http://leep2006.blog.sohu.com/68984508.html