Date & time
DATE
✔️ Safe to use in filter queries
Constructs and returns a date & time value given the date’s constituent properties.
Syntax
DATE(year, month, day, hour, minute, second, millisecond, timezone)
| Name | Type | Description |
|---|---|---|
| year | mandatory | Calendar year, value range: 1-9999. Can be any expression that evaluates to a number. |
| month | optional | Month, value range: 1-12, defaults to 1 if not provided. Can be any expression that evaluates to a number. |
| day | optional | Day of month, value range: 1-31, defaults to 1 if not provided. Can be any expression that evaluates to a number. |
| hour | optional | Hour, value range: 0-23, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| minute | optional | Minute, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| second | optional | Second, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| millisecond | optional | Millisecond, value range: 0-999, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
DATEADD
✔️ Safe to use in filter queries
Adds a period of time to the input date & time value and returns the resulting date & time value.
Syntax
DATEADD(date, duration, unit_of_measure)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value that the duration will be added to. |
| duration | mandatory | The duration to add to the date & time value. Can be any expression that evaluates to a positive or negative numeric value. If the unit of measure is not specified, by default adds "days" to the date & time value. |
| unit_of_measure | optional | The unit of measure of duration. Can be either "years", "months", "days", "weeks", "hours", "minutes", "seconds" or "milliseconds" |
DATEDIFF
❌ Cannot be used in filter queries
Calculates the difference between two date & time values as a duration. If the unit of measure is not specified, calculates the difference between end and start dates in days.
Syntax
DATEDIFF(start_date, end_date, unit_of_measture)
| Name | Type | Description |
|---|---|---|
| start_date | mandatory | The start date & time value. Can be any expression that resolves to a date & time value. |
| end_date | mandatory | The end date & time value. Can be any expression that resolves to a date & time value. |
| unit_of_measure | optional | The unit of measure of date difference. Can be either "years", "months", "days", "weeks", "hours", "minutes", "seconds" or "milliseconds" |
DATEISO
✔️ Safe to use in filter queries
Constructs and returns a date & time value given the date’s constituent properties.
Syntax
DATEISO(isoWeekYear, isoWeek, isoDayOfWeek, hour, minute, second, millisecond, timezone)
| Name | Type | Description |
|---|---|---|
| isoWeekYear | mandatory | ISO week date year, value range: 1-9999. Can be any expression that evaluates to a number. |
| isoWeek | optional | Week of year, value range: 1-53, defaults to 1 if not provided. Can be any expression that evaluates to a number. |
| isoDayOfWeek | optional | Day of week (Monday 1 - Sunday 7), defaults to 1 if not provided. Can be any expression that evaluates to a number. |
| hour | optional | Hour, value range: 0-23, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| minute | optional | Minute, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| second | optional | Second, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| millisecond | optional | Millisecond, value range: 0-999, defaults to 0 if not provided. Can be any expression that evaluates to a number. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
DATEMILLIS
✔️ Safe to use in filter queries
Create a date & time value from a number of milliseconds since the epoch (meaning since 1 January 1970 00:00:00 UTC).
Syntax
DATEMILLIS(milliseconds)
| Name | Type | Description |
|---|---|---|
| milliseconds | mandatory | Number of milliseconds since 1970 UTC. Can be any expression that evaluates to a number. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
DATESTRING
✔️ Safe to use in filter queries
Converts a date & time string to a date & time value.
Syntax
DATESTRING(date_string, date_format)
| Name | Type | Description |
|---|---|---|
| date_string | mandatory | The date & time string to convert to a date & time value. If the date format is not specified, expects the date string to be either in ISO 8601 or RFC 2822 format. |
| date_format | optional | The date format specification of the date string. The format can be any expression that evaluates to a string value, containing 0 or more format specifiers such as "%m-%d-%Y" |
| Specifiers | Description | Possible Values |
|---|---|---|
| %d | Day of Month (2 digits, zero padded) | 01-31 |
| %G | Year in ISO 8601 format | 0000-9999 |
| %H | Hour (2 digits, zero-padded, 24-hour clock) | 00-23 |
| %L | Millisecond (3 digits, zero padded) | 000-999 |
| %m | Month (2 digits, zero padded) | 01-12 |
| %M | Minute (2 digits, zero padded) | 00-59 |
| %S | Second (2 digits, zero padded) | 00-60 |
| %u | Day of week number in ISO 8601 format (1-Monday, 7-Sunday) | 1-7 |
| %V | Week of Year in ISO 8601 format | 1-53 |
| %Y | Year (4 digits, zero padded) | 0000-9999 |
| %z | The timezone offset from UTC. | +/-[hh]:[mm] |
DATETOSTRING
✔️ Safe to use in filter queries
Converts a date & time value to a string according to a user-specified format.
Syntax
DATETOSTRING(date, date_format)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value to convert to string. Can be a valid expression that resolves to a date & time value. |
| date_format | optional | The date format specification of the date string. The format can be any expression that evaluates to a string value, containing 0 or more format specifiers such as "%m-%d-%Y". If unspecified, DATETOSTRING uses "%Y-%m-%dT%H:%M:%S.%LZ" as the default format. |
| Specifiers | Description | Possible Values |
|---|---|---|
| %d | Day of Month (2 digits, zero padded) | 01-31 |
| %G | Year in ISO 8601 format | 0000-9999 |
| %H | Hour (2 digits, zero-padded, 24-hour clock) | 00-23 |
| %L | Millisecond (3 digits, zero padded) | 000-999 |
| %m | Month (2 digits, zero padded) | 01-12 |
| %M | Minute (2 digits, zero padded) | 00-59 |
| %S | Second (2 digits, zero padded) | 00-60 |
| %u | Day of week number in ISO 8601 format (1-Monday, 7-Sunday) | 1-7 |
| %V | Week of Year in ISO 8601 format | 1-53 |
| %Y | Year (4 digits, zero padded) | 0000-9999 |
| %z | The timezone offset from UTC. | +/-[hh]:[mm] |
DATEVALUE
✔️ Safe to use in filter queries
Converts a value to a date & time. If the value cannot be converted to a date & time, DATEVALUE fails with an error.
Syntax
DATEVALUE(date_value)
| Name | Type | Description |
|---|---|---|
| date_value | mandatory | The value to convert to date & time. If the value is numeric then creates the date & time assuming the provided value is in milliseconds since the epoch (meaning since 1 January 1970 00:00:00 UTC). If the value is string (text) then expects the value string to be either in ISO 8601 or RFC 2822 format. |
DAY
✔️ Safe to use in filter queries
Returns the day of the month for a date as a number between 1 and 31.
Syntax
DAY(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
HOUR
✔️ Safe to use in filter queries
Returns the hour part of a date as a number between 0 and 23.
Syntax
HOUR(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
ISOWEEKDAY
✔️ Safe to use in filter queries
Returns the weekday number, ranging from 1 (for Monday) to 7 (for Sunday).
Syntax
ISOWEEKDAY(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
MILLISECOND
✔️ Safe to use in filter queries
Returns the millisecond part of a date as an integer between 0 and 999.
Syntax
MILLISECOND(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
MINUTE
✔️ Safe to use in filter queries
Returns the minute part of a date as an integer between 0 and 59.
Syntax
MINUTE(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
MONTH
✔️ Safe to use in filter queries
Returns the month of a date as a number between 1 and 12.
Syntax
MONTH(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
NOW
✔️ Safe to use in filter queries
Returns the current date & time.
Syntax
NOW()
SECOND
✔️ Safe to use in filter queries
Returns the second part of a date as a number between 0 and 59.
Syntax
SECOND(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
WEEK
✔️ Safe to use in filter queries
Returns the week number of the date, ranging from 1 to 53.
Syntax
WEEK(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
WEEKDAY
✔️ Safe to use in filter queries
Returns the day of the week for a date as a number between 1 (Sunday) and 7 (Saturday)
Syntax
WEEKDAY(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
WEEKYEAR
✔️ Safe to use in filter queries
Returns the year number in ISO 8601 format.
Syntax
WEEKYEAR(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
YEAR
✔️ Safe to use in filter queries
Returns the year portion of a date.
Syntax
YEAR(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |
YEARDAY
✔Safe to use in filter queries
Returns the day of the year for a date as a number between 1 and 366.
Syntax
YEARDAY(date, timezone)
| Name | Type | Description |
|---|---|---|
| date | mandatory | The date & time value. Can be any expression that resolves to a date & time value. |
| timezone | optional | Timezone, can be any expression that evaluates to a string whose value is either an Olson Timezone Identifier, such as "Europe/London" or "America/New_York", or a UTC offset in the form of +/-[hh]:[mm], e.g., "+04:45", "-05:30" |