Jaa

DATEADD (Transact-SQL)

  • Artikkeli

Returns a new datetime value based on adding an interval to the specified date.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATEADD (datepart , number, date )

Arguments

  • datepart
    Is the parameter that specifies on which part of the date to return a new value. The following table lists the dateparts and abbreviations recognized by Microsoft SQL Server 2005.

    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
  • number
    Is the value used to increment 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 and1.75 for number, date is incremented by 1.

  • date
    Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. For more information about specifying dates, see Date and Time (Transact-SQL).

    If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that comes before the cutoff year. For example, if two-digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types

Returns datetime, but smalldatetime if the date argument is smalldatetime.

Remarks

When datepart is month, the number of days in the month affects the result. For example, August has 31 days and September has 30 days. Both of the following statements return 2006-09-30 00:00:00.000.

SELECT DATEADD(month, 1, '08/30/2006')
SELECT DATEADD(month, 1, '08/31/2006')

In other words, adding one month to the end of August returns the last day of September.

Examples

The following example prints a listing of a time frame for orders in the AdventureWorks database. This time frame represents the existing order date plus 21 days.

USE AdventureWorks;
GO
SELECT DATEADD(day, 21, OrderDate)AS TimeFrame 
FROM Sales.SalesOrderHeader;
GO

See Also

Reference

CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
Date and Time Functions (Transact-SQL)

Other Resources

ISO 8601 Format
Alphabetic Date Format
Numeric Date Format
ODBC Datetime Format
Time Formats
Unseparated String Format

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added the Remarks section.