Skip to main content

DATEADD (SQL)

A date/time function that returns a timestamp calculated by adding or subtracting a number of date part units (such as hours or days) to a date or timestamp.

Synopsis

DATEADD(datePart,numUnits,date)

Description

  • DATEADD(datePart,numUnits,date) modifies a date or time expression by incrementing the specified date part by the specified number of units. If you specify negative units, then DATEADD decrements the date by that number of units.

    This statement increments the current date by 5 months.

    SELECT DATEADD('month',5,CURRENT_DATE)

DATEADD is compatible with Sybase and Microsoft SQL Server.

Arguments

datePart

The full or abbreviated name of a date or time part. You can specify datePart in uppercase or lowercase. In embedded SQL, specify datePart as a literal value or host variable. This table shows the valid date and time part names and abbreviations. It also shows by how much a single unit of that part (numUnits = 1) increments the date.

Name Abbreviations numUnits = 1
year yyyy, yy Increments year by 1.
quarter qq, q Increments month by 3.
month mm, m Increments month by 1.
week wk, ww Increments day by 7.
weekday dw Increments day by 1.
day dd, d Increments day by 1.
dayofyear dy Increments day by 1.
hour hh Increments hour by 1.
minute mi, n Increments minute by 1.
second ss, s Increments second by 1.
millisecond ms Increments second by 0.001 (precision of 3)
microsecond mcs Increments second by 0.000001 (precision of 6)
nanosecond ns Increments second by 0.000000001 (precision of 9)

Incrementing or decrementing a date part causes other date parts to be modified appropriately. For example, incrementing the hour past midnight automatically increments the day, which may in turn increment the month, and so forth.

DATEADD performs slightly different operations depending on whether you specify datePart with or without quotes:

  • Quotes: DATEADD('month',1,'2022-02-25'): datePart is treated as a literal. When processing the query, InterSystems SQL performs literal substitution, replacing the 'month' string with an input parameter, which produces a more generally reusable cached query.

  • No quotes: DATEADD(month,1,'2022-02-25'): datePart is treated as a keyword. When processing the query, InterSystems SQL does not perform literal substitution, which produces a more specific cached query.

Specifying an invalid datePart literal value generates an SQLCODE -8 error code. However, if you supply an invalid datePart value as a host variable, no SQLCODE error is issued and the DATEPART function that is called to parse datePart returns a value of NULL.

numUnits

The number of datePart units added to or subtracted from date, specified as a numeric value. DATEADD truncates numUnits to an integer. If numUnits contains no numeric parts or does not start with a numeric value, DATEADD truncates this value to 0 and returns the originally specified date.

date

The date or time expression being added to or subtracted from, specified as one of these InterSystems IRIS® data types:

  • %Date logical value (+$H), also known as $HOROLOG format.

  • %PosixTime (%Library.PosixTime) logical value (an encoded 64-bit signed integer).

  • %TimeStamp (%Library.TimeStamp) logical value (YYYY-MM-DD HH:MM:SS.FFF), also known as ODBC format.

  • %String or string-compatible value, which can be in one of these formats:

    $HOROLOG Date and Time Format
    Format Example
    ddddd SELECT DATEADD('yy',1,'66716')
    ddddd,sssss SELECT DATEADD('yy',1,'66716,256')
    ddddd,sssss.fff SELECT DATEADD('yy',1,'66716,256.467')

    where:

    • ddddd is the integer number of days since December 31, 1840.

    • sssss is the integer number of seconds since the start of that day.

    • fff is the integer number of fractional seconds. If you specify fractional seconds, the returned DATEADD value also includes fractional seconds.

    Date Format
    Format Example
    MM/DD/YY SELECT DATEADD('year',1,'12/31/99')
    MM/DD/YYYY SELECT DATEADD('year',1,'8/24/2022')
    MM-DD-YY SELECT DATEADD('year',1,'12-31-99')
    MM-DD-YYYY SELECT DATEADD('year',1,'8-24-2022')
    MM.DD.YY SELECT DATEADD('year',1,'12.31.99')
    MM.DD.YYYY SELECT DATEADD('year',1,'8.24.2022')
    Mmm DD YY SELECT DATEADD('year',1,'Dec 30 92')
    Mmm DD YYYY SELECT DATEADD('year',1,'January 23 2021')
    Mmm DD, YY SELECT DATEADD('year',1,'Dec 30, 92')
    Mmm DD, YYYY SELECT DATEADD('year',1,'January 23, 2021')

    where:

    • MM is the two-digit month.

    • DD is the two-digit number of days in the month.

    • Mmm is the spelled-out month. You can specify a minimum of three letters (for example, Mar) up to the full month name (for example, March).

    • YY and YYYY are the two-digit and four-digit forms of the year, respectively.

    You can specify date as a combined date and time string. For example:

    SELECT DATEADD('hh',1,'12/22/2021 8:15:23')

    If you specify a time without a date, DATEADD defaults to date 01/01/1900.

    Time Format
    Format Example
    HH: SELECT DATEADD('hour',1,'10:')
    HH:MM SELECT DATEADD('mi',1,'10:30')
    HH:MM:SS SELECT DATEADD('ss',1,'10:30:59')
    HH...SS.FFF SELECT DATEADD('ms',1,'10:30:59.245')
    HH...[AM|PM] SELECT DATEADD('mi',1,'10:30PM')

    where:

    • HH is the two-digit number of hours into the day.

    • MM is the two-digit number of minutes into the hour.

    • SS is the two-digit number of seconds into the minute.

    • FFF is the number of fractional seconds.

    You can specify date as a combined date and time string. For example:

    SELECT DATEADD('hh',1,'12/22/2021 8:15:23')

    If you specify a date without a time, DATEADD defaults to time 00:00:00.

The date argument has these restrictions and behaviors:

  • The date string must be complete and properly formatted with the appropriate number of elements and digits for each element and the appropriate separator character. Years must be specified as four digits. If you omit the date portion of an input value, DATEADD defaults to '1900-01-01'.

  • Date and time values must be within the valid range:

    • Years — 0001 through 9999

    • Months — 1 through 12

    • Days — 1 through 31

    • Hours — 00 through 23

    • Minutes — 0 through 59

    • Seconds — 0 through 59

    The number of days in a month must match the month and year. For example, the date '02-29' is valid only if the specified year is a leap year.

  • In date values less than 10 (month and day) a leading zero in optional. Other non-canonical integer values are not permitted. For example, a Day value of '07' or '7' is valid, but '007', '7.0', or '7a' are not valid.

  • Time values are optional. If date specifies an incomplete time, zeros are supplied for the unspecified parts.

  • An hour value less than 10 must include a leading zero.

Examples

Add Varying Time Units to Dates

This statement adds 1 week to the specified date. It returns 2022-03-05 00:00:00, because adding 1 week adds 7 days. DATEADD supplies the omitted time portion.

SELECT DATEADD('week',1,'2022-02-26') AS NewDate

The statement adds 5 months to the specified timestamp and returns 2022-04-26 12:00:00. DATEADD modifies both the month and year, because adding 5 months also increments the year.

SELECT DATEADD(MM,5,'2021-11-26 12:00:00') AS NewDate

This statement also adds 5 months to the timestamp and returns 2021-06-30 12:00:00. DATEADD modifies both the day and month, because incrementing only the month results in an invalid date of June 31.

SELECT DATEADD('mm',5,'2021-01-31 12:00:00') AS NewDate

This statement adds 45 minutes to the timestamp and returns 2022-02-26 12:45:00.

SELECT DATEADD(MI,45,'2022-02-26 12:00:00') AS NewTime

This statement also adds 45 minutes to the timestamp, but in this case the addition increments the day, which increments the month. It returns 2022-03-01 00:15:00.

SELECT DATEADD('mi',45,'2022-02-28 23:30:00') AS NewTime

This statement decrements the original timestamp by 45 minutes and returns 2021-12-31 23:25:00.

SELECT DATEADD(N,-45,'2022-01-01 00:10:00') AS NewTime

This statement adds 60 days to the current date, adjusting for the varying lengths of months.

SELECT DATEADD(D,60,CURRENT_DATE) AS NewDate

The first DATEADD of this statement adds 92 days to the specified date and returns 2022-03-22 00:00:00. The second DATEADD adds 1 quarter to the specified date and returns 2022-03-20 00:00:00. Incrementing by a quarter increments the month field by 3. If necessary, DATEADD also increments the year field and corrects for the maximum number of days for a given month.

SELECT DATEADD('dd',92,'2021-12-20') AS NewDateD,
       DATEADD('qq',1,'2021-12-20') AS NewDateQ

The previous statements all use date part abbreviations. However, you can also specify the date part by its full name. For example, this statement adds 92 days to the date and returns 2022-03-22 00:00:00.

SELECT DATEADD('day',92,'2021-12-20') AS NewDate

This Embedded SQL code uses host variables to perform the same DATEADD operation as the previous SQL statement.

  set datePart = "day"
  set numUnits = 92
  set dateIn = "2021-12-20"

  &sql(SELECT DATEADD(:datePart,:numUnits,:dateIn) INTO :dateOut)

  write "in: ",dateIn,!,"out: ",dateOut

Alternatives

You can perform time and date modifications using the TIMESTAMPADD ODBC scalar function.

To perform equivalent timestamp conversions in ObjectScript, use the DATEADD() method:

$SYSTEM.SQL.Functions.DATEADD(datePart,numUnits,date)

See Also

Feedback