Date Functions

Date functions allow you to work with dates and times, extract components, calculate differences, and perform date arithmetic.

ConvertTimezone

Converts datetime values to a specified time zone.

Category: date

Syntax:

ConvertTimezone([date], "timezone", |from_timezone|)

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date	column\|datetime	✓ Yes	The datetime value to convert.
timezone	text	✓ Yes	The TZ identifier of the IANA time zone to which the datetime value is converted.
from_timezone	text	✗ No	Optional TZ identifier of the IANA time zone from which the datetime value is converted. Defaults to the organization account time zone when unspecified.

Allowed Column Types: DATETIME

Validation

• Minimum parameters: 2
• Maximum parameters: 3

Examples

ConvertTimezone([Date], "America/Los_Angeles")

Interprets values in the Date column as datetime values in the organization account time zone, then converts them to the equivalent datetime values in America/Los_Angeles (Pacific) time.

ConvertTimezone(Date("2014-07-18 10:58:00"), "America/Los_Angeles", "America/New_York")

Interprets 2014-07-18 10:58:00 in America/New_York (Eastern) time and converts it to the equivalent time in America/Los_Angeles (Pacific). Result: 2014-07-18 07:58:00

---

DateAdd

Adds a specified amount of time to a date.

Category: date

Syntax:

DateAdd("unit", amount, [date])

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
unit	text	✓ Yes	The unit of time to add.
amount	number	✓ Yes	The number of units to add. Decimal values are rounded to the nearest integer.
date	column\|datetime	✓ Yes	The date value or column of date values to which time is added.

Allowed Values for unit: year, quarter, month, week, day, hour, minute, second

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 3
• Maximum parameters: 3

Examples

DateAdd("minute", 60, Date("1999-12-31 23:00:00"))

Adds 60 minutes to the specified date. Result: 2000-01-01 00:00:00

DateAdd("day", 7, [Date])

Adds seven days to every date in the Date column.

DateAdd("year", -1, [Date])

Subtracts one year from every date in the Date column.

---

DateDiff

Calculates the time difference between two dates.

Important Note

The result is rounded to the nearest integer. If the ending date precedes the starting date, the output is a negative integer.

Category: date

Syntax:

DateDiff("unit", [start], [end])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
unit	text	✓ Yes	The unit to measure the difference.
start	column\|datetime	✓ Yes	The starting date (use Date() to convert if needed).
end	column\|datetime	✓ Yes	The ending date (use Date() to convert if needed).

Allowed Values for unit: year, quarter, month, week, day, hour, minute, second, millisecond

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 3
• Maximum parameters: 3

Examples

DateDiff("day", [Invoice Date], Today())

Days between the Invoice Date column and current UTC date.

DateDiff("year", [Invoice Date], Date("2018-01-01"))

Years between Invoice Date and 2018-01-01. Result: 8

DateDiff("hour", [Ticket Date], Now())

Hours between Ticket Date and current UTC time. Result: 103

Related Functions

• DateAdd - Add or subtract time from a date
• Today - Get current date
• Now - Get current date and time

---

DateFromUnix

Converts a Unix timestamp to a Date data type.

Category: date

Syntax:

DateFromUnix([number])

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
number	number\|column	✓ Yes	A number or column of numbers representing a Unix timestamp (seconds or milliseconds).

Allowed Column Types: INT, FLOAT, DECIMAL

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

DateFromUnix(0)

Epoch start. Result: 1970-01-01 00:00:00

DateFromUnix(1503724894)

Converts seconds since epoch. Result: 2017-08-26 05:21:34

---

DatePart

Extracts the specified date part from a date value.

Category: date

Syntax:

DatePart("precision", [date], |timezone|)

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
precision	text	✓ Yes	The date part to extract.
date	column\|datetime	✓ Yes	Date or column of dates from which to extract the date part.
timezone	text	✗ No	Optional IANA time zone name (e.g., "America/Los_Angeles"). Defaults to UTC.

Allowed Values for precision: year, quarter, month, week, day, weekday, day_of_year, hour, minute, second, millisecond, epoch

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 2
• Maximum parameters: 3

Examples

DatePart("year", [Invoice Date])

Extracts the year from each value in the invoice date column.

DatePart("week", Date("2007-01-10 10:00:00"))

Returns the week number for the given date (weeks start on Sunday by default). Result: 2

---

DateTrunc

Truncates the date to the specified date part.

Category: date

Syntax:

DateTrunc("part", [date_column])

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
part	text	✓ Yes	The part to truncate to (year, month, day, etc.).
date_column	column\|datetime	✓ Yes	The date to truncate.

Allowed Values for part: year, quarter, month, week, day, hour, minute, second

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 2
• Maximum parameters: 2

Examples

DateTrunc("month", [OrderDate])

Truncates to first day of month.

---

Day

Returns the day of the month of a date value as a number.

Category: date

Syntax:

Day([date_column], |timezone|)

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date from which to extract the day component.
timezone	text	✗ No	Optional IANA time zone name (e.g., "Europe/Amsterdam").

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 2

Examples

Day([Date])

Returns the day of the month of each date value in the [Date] column as a number.

---

DayOfYear

Converts a date to the number day of the year, from 1 (Jan 1) to 365 (Dec 31). Accounts for leap years.

Category: date

Syntax:

DayOfYear([date], |timezone|)

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date	column\|datetime	✓ Yes	The date or column of date values to evaluate.
timezone	text	✗ No	Optional IANA time zone name (e.g., "Europe/Amsterdam").

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 2

Examples

DayOfYear(Date("2023-01-01"))

Converts Jan 1, 2023 to the number day of the year. Result: 1

DayOfYear(Date("2023-12-31"))

Converts Dec 31, 2023 to the number day of the year. Result: 365

DayOfYear(Date("2024-12-31"))

Converts Dec 31, 2024 (leap year) to the number day of the year. Result: 366

DayOfYear(Date("2023-01-03 02:00:00Z"), "Europe/Amsterdam")

Converts Jan 3, 2023 02:00:00Z to the number day of the year in New York. Result: 2

---

EndOfMonth

Returns the last day of the month of a Date value.

Category: date

Syntax:

EndOfMonth([date])

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date	column\|datetime	✓ Yes	The date from which the end of month is to be computed.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

EndOfMonth([Invoice Date])

Extract the last day of the month from the Invoice Date column.

EndOfMonth(Date("2023-03-07"))

Returns end of month for March 2023. Result: 2023-03-31 23:59:59

---

Hour

Returns the hour component of the given Date as a number.

Category: date

Syntax:

Hour([date_column], |timezone|)

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The Date from which to extract the hour component.
timezone	text	✗ No	Name of IANA time zone to get the date part at (e.g., "America/Los_Angeles"). When calculating a time zone, input dates are treated as UTC.

Allowed Column Types: DATETIME

Validation

• Minimum parameters: 1
• Maximum parameters: 2

Examples

Hour(Date("2007-08-14 07:11:00"))

Returns hour from the given date value. Result: 7

Hour([Date])

Returns the hour component of each date value in the [Date] column as a number.

---

InDateRange

Determines if a date is within a specified date range.

Category: date

Syntax:

InDateRange([date_column], "direction", "period", |length|, |offset|, |today|)

Returns: BooleanColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date column	column\|datetime	✓ Yes	The date to be evaluated.
direction	text	✓ Yes	Direction to apply the date range offset.
period	text	✓ Yes	Size of the date window.
length	number	✗ No	Number of periods to include in the range. Defaults to 1.
offset	number	✗ No	Number of periods to offset the start of the range. Defaults to 0.
today	column\|datetime	✗ No	Override value for Today when calculating relative to current date.

Allowed Values for direction: last, next, current, to_date

Allowed Values for period: year, quarter, month, week, day, hour, minute, second

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 3
• Maximum parameters: 6

Examples

InDateRange([Date], "current", "month")

Returns True for all dates in the current month.

InDateRange([Date], "last", "week", 3)

Returns True for all dates in the last three weeks.

InDateRange([Date], "last", "week", 1, 3)

Returns True for all dates in the week starting three weeks ago.

InDateRange([Date], "to_date", "year", 1, -1)

Returns True for all dates within the last year up to today's date.

---

InPriorDateRange

Determines if a date is within a specified date range in a previous time period.

Category: date

Syntax:

InPriorDateRange([date_column], "range_period", "prior_period", |offset|, |today|)

Returns: BooleanColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date column	column\|datetime	✓ Yes	The date to be evaluated.
range period	text	✓ Yes	The period for the date range.
prior period	text	✓ Yes	The period used to offset the range period.
offset	number	✗ No	Number of prior periods to offset by. Defaults to 1.
today	column\|datetime	✗ No	Override value for Today when calculating relative to current date.

Allowed Values for range period: year, quarter, month, week, day, hour, minute, second

Allowed Values for prior period: year, quarter, month, week, day, hour, minute, second

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 3
• Maximum parameters: 5

Examples

InPriorDateRange([Date], "hour", "day")

Returns True for all dates in the current hour of the day in the previous day.

InPriorDateRange([Date], "month", "year")

Returns True for all dates in this month last year.

InPriorDateRange([Date], "month", "year", 2)

Returns True for all dates in this month two years ago.

---

LastDay

Evaluates a specified date component in a date value and returns the last date and time in that component as a timestamp.

Category: date

Syntax:

LastDay([date], "precision")

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date	column\|datetime	✓ Yes	The date or column of date values from which the date component is extracted.
precision	text	✓ Yes	The date component to evaluate for its last date and time.

Allowed Values for precision: year, quarter, month, week

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 2
• Maximum parameters: 2

Examples

LastDay([Invoice Date], "month")

Extracts the month component and returns the last date and time for that month.

LastDay(Date("2020-01-10 10:34:29"), "year")

Returns the last date and time for the year of the given date.

---

MakeDate

Evaluates specified year, month, day (and optional hour, minute, second) components and returns a datetime value in ISO format.

Category: date

Syntax:

MakeDate(year, month, day, |hour|, |minute|, |second|)

Returns: DateTimeColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
year	number	✓ Yes	An integer representing the year.
month	number	✓ Yes	An integer representing the month (1-12).
day	number	✓ Yes	An integer representing the day of the month (1-31).
hour	number	✗ No	An integer representing the hour (0-23).
minute	number	✗ No	An integer representing the minutes (0-59).
second	number	✗ No	An integer representing the seconds (0-59).

Validation

• Minimum parameters: 3
• Maximum parameters: 6

Examples

MakeDate(2019, 1, 31, 16, 30, 0)

Returns the datetime value. Result: 2019-01-31 16:30:00

---

Minute

Returns an integer representing the minute component of a specified date and time.

Category: date

Syntax:

Minute([date_column], |timezone|)

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date or column containing date values from which the minute component is extracted.
timezone	text	✗ No	The IANA time zone (e.g., "America/Los_Angeles") to convert the date before extracting the minute. If omitted, evaluated in UTC.

Allowed Column Types: DATETIME

Validation

• Minimum parameters: 1
• Maximum parameters: 2

Examples

Minute(Date("2007-08-14 07:11:00"))

Extracts the minute from the specified date and time. Result: 11

Minute([Date])

Returns the minute component for each value in the [Date] column.

---

Month

Returns an integer representing the month component in a specified datetime value.

Category: date

Syntax:

Month([date_column])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract month from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

Month([OrderDate])

Returns month (1-12) from date.

---

MonthName

Returns the name of the month component from a specified datetime value.

Category: date

Syntax:

MonthName([date_column])

Returns: StringColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract month name from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

MonthName([OrderDate])

Returns month name (e.g., 'January').

---

Now

Returns the current date and time using your organization's account timezone.

Category: date

Syntax:

Now()

Returns: DateTimeColumn

Context Filtering: ✗ No

Parameters

None

Validation

• Minimum parameters: 0
• Maximum parameters: 0

Examples

Now()

Returns current date and time.

---

Quarter

Returns an integer representing the quarter component in a specified datetime value.

Category: date

Syntax:

Quarter([date_column])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract quarter from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

Quarter([OrderDate])

Returns quarter (1-4) from date.

---

Second

Returns an integer representing the second component of a specified date and time.

Category: date

Syntax:

Second([date_column])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date or column containing date values from which the second component is extracted.

Allowed Column Types: DATETIME

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

Second(Date("2007-08-14 07:11:05"))

Extracts the second from the specified date and time. Result: 5

Second([Date])

Returns the second component for each value in the [Date] column.

---

Today

Returns the current date in ISO format using the Account Time Zone.

Category: date

Syntax:

Today()

Returns: DateColumn

Context Filtering: ✗ No

Parameters

None

Validation

• Minimum parameters: 0
• Maximum parameters: 0

Examples

Today()

Returns the current date.

---

Weekday

Returns an integer representing the day of the week in a specified datetime value.

Category: date

Syntax:

Weekday([date_column])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract weekday from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

Weekday([OrderDate])

Returns day of week (1=Sunday, 7=Saturday).

---

WeekdayName

Returns the name of the day of the week in a specified datetime value.

Category: date

Syntax:

WeekdayName([date_column])

Returns: StringColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract weekday name from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

WeekdayName([OrderDate])

Returns weekday name (e.g., 'Monday').

---

Year

Returns an integer representing the year component in a specified datetime value.

Category: date

Syntax:

Year([date_column])

Returns: IntColumn

Context Filtering: ✓ Yes

Parameters

Name	Type	Required	Description
date_column	column\|datetime	✓ Yes	The date to extract year from.

Allowed Column Types: DATETIME, DATE

Validation

• Minimum parameters: 1
• Maximum parameters: 1

Examples

Year([OrderDate])

Returns year from date.