DateTimeAdd (NoSQL query)

  • Article

APPLIES TO: NoSQL

Returns a date and time string value that is the result of adding a specified number value to the provided date and time string.

Syntax

DateTimeAdd(<date_time_part>, <numeric_expr> ,<date_time>)

Arguments

Description
date_time_part A string representing a part of an ISO 8601 date format specification. This part is used to indicate which aspect of the date to modify by the related numeric expression.
numeric_expr A numeric expression resulting in a signed integer.
date_time A Coordinated Universal Time (UTC) date and time string in the ISO 8601 format YYYY-MM-DDThh:mm:ss.fffffffZ.

Return types

Returns a UTC date and time string in the ISO 8601 format YYYY-MM-DDThh:mm:ss.fffffffZ.

Examples

The following example adds various values (one year, one month, one day, one hour) to the date July 3, 2020 at midnight (00:00 UTC). The example also subtracts various values (two years, two months, two days, two hours) from the same date. Finally, this example uses an expression to modify the seconds of the same date.

SELECT VALUE {
    addOneYear: DateTimeAdd("yyyy", 1, "2020-07-03T00:00:00.0000000"),
    addOneMonth: DateTimeAdd("mm", 1, "2020-07-03T00:00:00.0000000"),
    addOneDay: DateTimeAdd("dd", 1, "2020-07-03T00:00:00.0000000"),
    addOneHour: DateTimeAdd("hh", 1, "2020-07-03T00:00:00.0000000"),
    subtractOneYear: DateTimeAdd("yyyy", -1, "2020-07-03T00:00:00.0000000"),
    subtractOneMonth: DateTimeAdd("mm", -1, "2020-07-03T00:00:00.0000000"),
    subtractOneDay: DateTimeAdd("dd", -1, "2020-07-03T00:00:00.0000000"),
    subtractOneHour: DateTimeAdd("hh", -1, "2020-07-03T00:00:00.0000000"),
    modifySecondsExpression: DateTimeAdd("ss", 5 * -5, "2020-07-03T00:00:00.0000000")
}

[
  {
    "addOneYear": "2021-07-03T00:00:00.0000000Z",
    "addOneMonth": "2020-08-03T00:00:00.0000000Z",
    "addOneDay": "2020-07-04T00:00:00.0000000Z",
    "addOneHour": "2020-07-03T01:00:00.0000000Z",
    "subtractOneYear": "2019-07-03T00:00:00.0000000Z",
    "subtractOneMonth": "2020-06-03T00:00:00.0000000Z",
    "subtractOneDay": "2020-07-02T00:00:00.0000000Z",
    "subtractOneHour": "2020-07-02T23:00:00.0000000Z",
    "modifySecondsExpression": "2020-07-02T23:59:35.0000000Z"
  }
]

Remarks

  • This function returns undefined for these reasons:
    • The specified date and time part is invalid.
    • The numeric expression isn't a valid integer.
    • The date and time in the argument isn't a valid ISO 8601 date and time string.
  • The ISO 8601 date format specifies valid date and time parts to use with this function: | | Format | | --- | --- | | Year | year, yyyy, yy | | Month | month, mm, m | | Day | day, dd, d | | Hour | hour, hh | | Minute | minute, mi, n | | Second | second, ss, s | | Millisecond | millisecond, ms | | Microsecond | microsecond, mcs | | Nanosecond | nanosecond, ns |

Related content