Date Transforms

Date Transform Functions Functions that operate on Date and DateTime fields and that manipulate years, months and days Purpose Use Date Transform functions to: Manipulate days, weeks and months in a date field Calculate elapsed time in da...

Date Transform: ABSDAYSDIFF(A,B) Returns absolute difference in days between 2 dates. Purpose Use this function to: Calculate the number of days between 2 dates Return Value Example Return Value: 0...N where N = maximum days diff...

Date Transform: AGE(A) Age in years at the time the field was built Number of whole years between Date(Time) field A and today, where today is the date on which the field was built. Purpose Use this function to: Calculate age of customer...

Date Transform: AGE(A,B) Number of whole years between the dates represented by A and B AGE(A,B) = B - A Purpose Use this function to: Calculate the age of a customer at a specific point in time Calculate the age of a transaction at a s...

Date Transform: ANNIVERSARYDAYS(A,B) Number of days between Day/Month(A) and Day/Month(B) as if A and B were both in the same year ANNIVERSARYDAYS ( A,B ) = DayOfYear ( B ) - DayOfYear ( A ) where DayOfYear ( B ) = number of day...

Date Transform: DADD(A,B) Returns the date created when adding B days to Date A Purpose Use this function to: Create new date fields according to mathematical rules Create dates that are a specified number of days before or after a source ...

Date Transform: DATE(A) Turns a continuous DateTime field into a discrete Date field, or creates a copy of a Date field. Purpose Use this function to: Create a copy of an existing date field Create a discrete Date field from a continuous D...

Date Transform: DATEFROM(A,B,C) Create a Date Field from Year(A), Month(B) and Day(C) Purpose Use this function to: Construct date fields from integer parts Return Value Example Return Value: 2019-05-08 Parameters JSON ...

Date Transform: Day(A) Returns the Day part of a Date or Timestamp as an integer from 1 to 31 Purpose Use this function to: Extract the date of the month from a date field Extract the date of the month from a time-stamp Return Value ...

Date Transform: DAYSAFTERMIN(A) Returns the difference in days between A and Min(A) Purpose Use this function to calculate: The number of days that have elapsed between an event and the earliest occurrence of that event The age of a transa...

Date Transform: DAYSBEFOREMAX(A) Returns the difference in days between A and Max(A) Purpose Use this function to calculate: The number of days that elapsed between an event and the latest occurrence of that event The number of days a...

Date Transform: DAYSDIFF(A,B) Returns the number of days difference between two dates, where A is taken away from B DAYSDIFF(A,B) = B - A Purpose Use this function to calculate: The number of days between two dates The elapsed time in d...

Date Transform: DAYSDIFF_MXONB(A) Returns the number of days between Date A and the maximum value of Date B Purpose Use this function to calculate: The number of days between a target date and a comparison date The number of days between o...

Date Transform: DOY(A) Returns Day of Year A, where Jan 01 is 1 and Dec 31 is 365/366 Purpose Use this function to: Return the number of elapsed days between A and the start of the year Return Value Example Return Value: 1...366...

Date Transform: MAX(A,B) Returns which ever date is the most recent between A and B Purpose Use this function to: Identify which of two events occurred most recently Return Value Example Return Value: 2012-01-05 Parameters...

Date Transform: MIN(A,B) Returns the earliest of two dates Purpose Use this function to: Identify which of two events occurred first Return Value Example Return Value: 2021-09-08 Parameters JSON Sample Usage Notes S...

Date Transform: MONTH(A) Returns the month portion of Date A as a value from 1 to 12 Purpose Use this function to: Extract the numeric month of the year from a date Extract the numeric month of the year from a time-stamp Return Value ...

Date Transform: SMONTH(A) Returns the name of the month in Date A as a string of format 01-January : 12-December Purpose Use this function to: Create Date/Month mappings Create user friendly month labels Return Value Example Ret...

Date Transform: SWEEKDAY(A) Returns the name of the weekday in Date A as a string of format 1-Monday : 7-Sunday Purpose Use this function to: Create Date/Weekday mappings Create user legible weekday labels Return Value Examp...

Date Transform: WEEKDAY(A) Returns the weekday from Date A as an integer from 1 to 7, where 1=Monday Purpose Use this function to: Create Date/Weekday mappings for use in mathematical formulas Group events into weekdays Return Value ...

Date Transform: WEEKOF(A,B) Groups Date B into weekly segments, where first week begins on Date A Purpose Use this function to: Group observations into weekly segments, starting on a fixed date Segment data for use in a time-series report,...

Date Transform: WOY(A) Returns Week of Year for Date A, as an integer between 1 and 52 Purpose Use this function to: Group transactions into week of year segments Segment events by week Group multi-year data into consistent weekly segment...

Date Transform: YEAR(A) Returns the year portion of Date A in the format YYYY Purpose Use this function to: Extract the year portion from a date Extract the year portion from a time-stamp Return Value Example Return Value: 2019,...

Date Transform: YEARMONTH(A) Extract the year and month from Date A in the format YYYYMM Purpose Use this function to: Segment multi-year data into months Return Value Example Return Value: 201905, 197409, 198112 Paramet...

Date Transform: YEARQTR(A) Extract the Year and Quarter from Date A in the format YYYYQ Purpose Use this function to: Segment Transaction data into yearly quarters Return Value Example Return Value: 20211,20191,20203 Param...

Date Transform: YEARWOY(A) Returns the year and week of year from Date A in the format YYYYWW Purpose Use this function to: Segment data into yearly weeks Return Value Example Return Value: YYYYWW, where YYYY = 0000....9999 WW...