Difference in formatting of date or date/time fields could lead to errors when you run your recipes. This article will explain the importance of putting in the right data format, how to differentiate between the different types of data and give some examples of common formulas you can use to format your date or date/time data pill.
Importance of proper formatting of your data
Some applications pass the date/time data field as a string while some requires the fields passed in a specific format. When we try to synchronize data between these apps, it is important to pass it in the correct format.
How to differentiate between different types of data (String vs Date/Time)
String type data is represented by the ABC icon while Date/Time type data is represented by the calendar and clock icon (shown below). The date only type data is represented with a calendar icon only.
1. To convert a STRING type data to DATE/TIME:
In formula mode, inputing ".to_time" after the data pill (shown above) will convert your string data to the date/time format. For example, [2014-11-21 17:37:27].to_time will return [2014-11-21T17:37:27.000000+00:00].
Do note that .to_time returns date/time in UTC format instead of your default timezone. Hence if your pill contains time zone element, it will be automatically converted to time in UTC. (Link the time zone article) For example, [2014-11-21 17:37:27 -0800].to_time will return [2014-11-22T01:37:27.000000+00:00].
1.1 Taking time zones into consideration
If you would like to specify the time zone in which you want the data pill to be in, add ".in_time_zone("timezone(TZ column)")". The examples below will return "2014-11-21T20:37:27.000000-05:00"
2. To convert a STRING type data to DATE:
In formula mode, inputing ".to_date" after the data pill (shown above) will convert the string to the DATE ONLY format. For example, [2014-11-21 17:37:27 -0800].to_date will return [2014-11-21].
Do note that if the target application expects any time or time zone element, the time of a date pill is automatically taken as [2014-11-21 00:00:00] in your Workato default time zone setting. (Link article to explain Workato default time zone)
3. Formatting your date or date/time pills
Use ".strftime" to format the date or date/time pill. For example, the output for the below example will be "November 21, 8:37PM".
If you want the output to be in "Friday, 21 November 2014 20:37" format, use this formula:
Below are the list of formatting formula you can use:
%Y — Year with century
%m — Month with zero-prefix
%B — Full month name
%b — Abbreviated month name
%d — Day of the month with zero-prefix
%e — Day of the month without zero-prefix
%H — Hour of the day (24-hour)
%k — Hour of day without 0 prefix (24-hour)
%I (capital i) — Hour of the day (12-hour)
%l (lowercase L) — Hour of day without 0 prefix (12-hour)
%p — AM or PM
%M — Minute of the hour
%S — Second of the minute
%z — Time zone offset from UTC (e.g. -0700)
%Z — Time zone abbrev. name