Converts a string or Unix epoch seconds to a date. Returns a date that can be used in another function such as daysBetween(). The returned date cannot be used in a filter.
Syntax
toDate(string, [”format"])
If a format argument isn’t provided, the function uses the format yyyy-MM-dd HH:mm:ss. format is only valid for converting date string values.
For the allowed formats, see Date Formats.
toDate(epoch_seconds)
The format argument is not valid for converting epoch_seconds numerical values.
note: Be sure to use the sec_epoch field and not the day_epoch field.
Example: Display the Exact Date an Opportunity Opened
Suppose that you want to see the exact day that an opportunity opened. Use toDate() with CreatedDate_Year, CreatedDate_Month, CreatedDate_Day, and an accepted date format. This query will resemble the example below:
q = load "oppty";
q = foreach q generate toDate(CreatedDate_Year + "/" + CreatedDate_Month + "/" + CreatedDate_Day, "yyyy/MM/dd") as CreatedDate;
q = order q by 'CreatedDate';
Example: Display the Number of Days Since an Opportunity Opened
Suppose that you have an opportunity dataset with the account name and the epoch seconds fields:
You want to see how many days ago an opportunity was opened. Use daysBetween() and now(). Use toDate() to convert the order date epoch seconds to a date format that can be passed to daysBetween().
q = load "OpsDates1";
q = foreach q generate Account, daysBetween(toDate(OrderDate_sec_epoch), now()) as 'daysOpened';
The resulting data stream displays the number of days since the opportunity was opened.
note: Because dates are sorted lexicographically, changing the date format also changes the sort order.
See Also