If you’ve been doing the dataflow into Data Recipe conversion or date manipulations using the Date/DateTime functions in recipes, you may have come across these what is the equivalent functions in recipe /error messages.
This post walks you through equivalent functions or ways to accomplish same functionality in Data Recipe
Consideration:-
If your org does not have custom timezone enabled, you should stick with using only Date Time type in recipes to avoid inadvertently causing any kinds of compatibility issues!
which functions should i use ??????????????????????????????
you would need to use date_trunc function for DateTime fields. and trunc function for Date fields.
“I need to calculate the year first day for a Date Time field”
syntax : date_trunc(‘format’,timestamp)
‘format’ is the date and time format pattern to follow. and all portions after it, to 01 for date and month , o for time part. in our use case we want to calculate year first date so we are keeping the ‘year’ part and resetting the rest of the months , days to 1.
This formula always returns 1st January.
“I need to calculate the year last day for a Date Time field”
syntax : date_trunc(‘format’,timestamp) + interval
The interval must be one of ‘yyyy’ (year), ‘q’ (quarter), ‘m’ (month), ‘d’ (day), ‘ww’ (week), ‘h’ (hour), ’n’ (minute), or ‘s’ (second).
in our use case we want to calculate year last date so we are keeping the ‘year’ part and resetting the rest of the months , days to 1.
example :
for the first record in a below screenshot date_trunc functions returns 2022–01–01.then we adding 1 year interval to the returned result ,output will be 2023–01–01.then subtracting the 1 day interval from the result ,our final output will become 2022–12–31.
This formula always returns 31st December.
“I need to calculate the month last day”
In data recipe we can be able to find month last day in two ways.
i) using Date_trunc functions for DateTime field.
ii) using last_day functions for Date field
month last day for a Date Time field
syntax : date_trunc(‘format’,timestamp) + interval-interval
The interval must be one of ‘yyyy’ (year), ‘q’ (quarter), ‘m’ (month), ‘d’ (day), ‘ww’ (week), ‘h’ (hour), ’n’ (minute), or ‘s’ (second).
in our use case we want to calculate last day of month so we are keeping the ‘month’ part and resetting the days to 1.
example :
for the first record in a below screenshot date_trunc functions returns 2022–07-01.then we adding 1 month interval to the returned result ,output will be 2022–08–01.then subtracting the 1 day interval from the result ,our final output will become 2022–07–31.
month last day for a Date field
syntax : last_day(date)
the last day function can only be used for date fields
I need to calculate the Number of days in months for a Date Time field
we dont have straight functions in recipe to calculate number of days in months . so we have to use couple of functions together to derive number of days in months. for the demonstration purpose i will go with step by step process where we will create 1additional columns but which is not needed really, we could achieve this by using combination function in single field. That will be given in second approach section.
1st approach :-
step 1 :-
we would need to calculate the last day of the specified date .
please refer the month last day section for explanation for this formula
step 2 :
since our target is to calculate the number of months in a specified date. After calculating month last date we can extract the ‘date’ components from ‘last day of month’ field using ‘day’ function to get the days in a month as ‘numeric’ data type.
syntax : day(field)
example : (1st records from below screenshot)
day(2022–07–31T07:27:36.000Z)
result :- 31
step 2 : optional for Number of Days in Months as ‘Text’ datatype
If you want to calculate Number of Days in months as ‘Text’ datatype then you could use ‘date_format’ functions to extract ‘day’ component from the ‘last day of month’ field.
syntax : date_format
example : (1st records from below screenshot)
date_format(2022–07–31T07:27:36.000Z,’dd’)
result :- 31
2nd approach :-
below is the final single column formula to calculate number of days in month as ‘Numeric’ datatype.
I need to compare months in a specified date against my business criteria
step 1 :-
In dataflow you could extract the date components like year,month,day,quarter, etc..in a specified date field by appending required components at the end of the date field .
ex : ‘CreatedDate_Month’
while in Recipe you would need to use the below functions to extract the date components
year(date)
day(date)
month(date)
quarter(date)
minute(timestamp)
second(timestamp)
