Scalar functions

DateTime/ Timespan functions

Function NameDescription
ago()Subtracts the given timespan from the current UTC clock time.
datetime_add()Calculates a new datetime from a specified datepart multiplied by a specified amount, added to a specified datetime.
datetime_part()Extracts the requested date part as an integer value.
datetime_diff()Calculates calendarian difference between two datetime values.
dayofmonth()Returns the integer number representing the day number of the given month
dayofweek()Returns the integer number of days since the preceding Sunday, as a timespan.
dayofyear()Returns the integer number represents the day number of the given year.
endofyear()Returns the end of the year containing the date, shifted by an offset, if provided.
getmonth()Get the month number (1-12) from a datetime.
getyear()Returns the year part of the datetime argument.
hourofday()

ago()

Subtracts the given timespan from the current UTC clock time.

Arguments

  • Interval to subtract from the current UTC clock time

Returns

now() - a_timespan

Example

ago(a_timespan)

datetime_add()

Calculates a new datetime from a specified datepart multiplied by a specified amount, added to a specified datetime.

Arguments

  • period: string.
  • amount: integer.
  • datetime: datetime value.

Returns

A date after a certain time/date interval has been added.

Example

datetime_add(period,amount,datetime)

datetime_part()

Extracts the requested date part as an integer value.

Arguments

  • date: datetime
  • part: string

Returns

An integer representing the extracted part.

Examples

datetime_part(part,datetime)

datetime_diff()

Calculates calendarian difference between two datetime values.

Arguments

  • period: string.
  • datetime_1: datetime value.
  • datetime_2: datetime value.

Returns

An integer, which represents amount of periods in the result of subtraction (datetime_1 - datetime_2).

Example

datetime_diff(period,datetime_1,datetime_2)

dayofmonth()

Returns the integer number representing the day number of the given month

Arguments

  • a_date: A datetime

Returns

day number of the given month.

Example

dayofmonth(a_date)

dayofweek()

Returns the integer number of days since the preceding Sunday, as a timespan.

Arguments

  • a_date: A datetime.

Returns

The timespan since midnight at the beginning of the preceding Sunday, rounded down to an integer number of days.

Example

dayofweek(a_date)

dayofyear()

Returns the integer number represents the day number of the given year.

Arguments

  • a_date: A datetime.

Returns

day number of the given year.

Example

dayofweek(a_date)

endofyear()

Returns the end of the year containing the date, shifted by an offset, if provided.

Arguments

  • date: The input date.

Returns

A datetime representing the end of the year for the given date value, with the offset, if specified.

Example

endofyear(date)

getmonth()

Get the month number (1-12) from a datetime.

getyear()

Returns the year part of the datetime argument.

Example

getyear(datetime())

hourofday()

Returns the integer number representing the hour number of the given date

Arguments

  • a_date: A datetime.

Returns

hour number of the day (0-23).

Example

hourofday(a_date)