Skip to main content

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)

NameTypeDescription
yearmandatoryCalendar year, value range: 1-9999. Can be any expression that evaluates to a number.
monthoptionalMonth, value range: 1-12, defaults to 1 if not provided. Can be any expression that evaluates to a number.
dayoptionalDay of month, value range: 1-31, defaults to 1 if not provided. Can be any expression that evaluates to a number.
houroptionalHour, value range: 0-23, defaults to 0 if not provided. Can be any expression that evaluates to a number.
minuteoptionalMinute, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number.
secondoptionalSecond, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number.
millisecondoptionalMillisecond, value range: 0-999, defaults to 0 if not provided. Can be any expression that evaluates to a number.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value that the duration will be added to.
durationmandatoryThe 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_measureoptionalThe 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)

NameTypeDescription
start_datemandatoryThe start date & time value. Can be any expression that resolves to a date & time value.
end_datemandatoryThe end date & time value. Can be any expression that resolves to a date & time value.
unit_of_measureoptionalThe 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)

NameTypeDescription
isoWeekYearmandatoryISO week date year, value range: 1-9999. Can be any expression that evaluates to a number.
isoWeekoptionalWeek of year, value range: 1-53, defaults to 1 if not provided. Can be any expression that evaluates to a number.
isoDayOfWeekoptionalDay of week (Monday 1 - Sunday 7), defaults to 1 if not provided. Can be any expression that evaluates to a number.
houroptionalHour, value range: 0-23, defaults to 0 if not provided. Can be any expression that evaluates to a number.
minuteoptionalMinute, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number.
secondoptionalSecond, value range: 0-59, defaults to 0 if not provided. Can be any expression that evaluates to a number.
millisecondoptionalMillisecond, value range: 0-999, defaults to 0 if not provided. Can be any expression that evaluates to a number.
timezoneoptionalTimezone, 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)

NameTypeDescription
millisecondsmandatoryNumber of milliseconds since 1970 UTC. Can be any expression that evaluates to a number.
timezoneoptionalTimezone, 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)

NameTypeDescription
date_stringmandatoryThe 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_formatoptionalThe 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"
SpecifiersDescriptionPossible Values
%dDay of Month (2 digits, zero padded)01-31
%GYear in ISO 8601 format0000-9999
%HHour (2 digits, zero-padded, 24-hour clock)00-23
%LMillisecond (3 digits, zero padded)000-999
%mMonth (2 digits, zero padded)01-12
%MMinute (2 digits, zero padded)00-59
%SSecond (2 digits, zero padded)00-60
%uDay of week number in ISO 8601 format (1-Monday, 7-Sunday)1-7
%VWeek of Year in ISO 8601 format1-53
%YYear (4 digits, zero padded)0000-9999
%zThe 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)

NameTypeDescription
datemandatoryThe date & time value to convert to string. Can be a valid expression that resolves to a date & time value.
date_formatoptionalThe 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.
SpecifiersDescriptionPossible Values
%dDay of Month (2 digits, zero padded)01-31
%GYear in ISO 8601 format0000-9999
%HHour (2 digits, zero-padded, 24-hour clock)00-23
%LMillisecond (3 digits, zero padded)000-999
%mMonth (2 digits, zero padded)01-12
%MMinute (2 digits, zero padded)00-59
%SSecond (2 digits, zero padded)00-60
%uDay of week number in ISO 8601 format (1-Monday, 7-Sunday)1-7
%VWeek of Year in ISO 8601 format1-53
%YYear (4 digits, zero padded)0000-9999
%zThe 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)

NameTypeDescription
date_valuemandatoryThe 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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)

NameTypeDescription
datemandatoryThe date & time value. Can be any expression that resolves to a date & time value.
timezoneoptionalTimezone, 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"