Transform Date Custom Value also known as the DATEADD() in SQL allows you to transform an existing date field by adding or subtracting datepart using signed integer +/- (eg. the number of hours, days, weeks, months) and/or converting it into a different timezone.
This greatly helps in many scenarios and challenges eg. creating call-to-action with an expiration date, generating new dates for reminder campaigns, etc.
Watch this video to learn more about Transform Date Custom Value.
How to create a Transform Date Custom Value?
- Go to Custom Values section > Add new value button
- Provide a name for the Custom Value > Apply formula to a field > Click Next > Transform Date > Click Next
- Select the Data Extension and the date field that you want to transform
- Under Add, indicate the integer necessary and select the time unit via the dropdown list
- To add a time unit, enter the value as is, e.g., 1
- To subtract a time unit, use the (-) sign, eg. -1
- Select which format to use either Date and Time or only Date
- In case you want to transform the date field's timezone, turn on the Convert timezone toggle and choose the timezones accordingly - From which timezone you are converting (current timezone of the date field) and To which timezone you want to convert
- Save and drag-and-drop the new Custom Value to the Target Data Extension fields
You have a Data Extension (Orders) that contains a column with Order Dates and you want to add a new field to your Target Data Extension that contains these Order Dates, but in a client's local timezone (e.g. CEST).
Follow these step-by-step instructions to achieve this.
You can also create reusable Custom Values. Follow this link to learn how to create reusable Custom Values.