Values in the following columns must be formatted as a Refine 'date':
...
Example date | Convert with |
---|---|
2012-12-31 (ISO8601) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
31/12/2012 (European style, day first - non ambiguous) | Can be converted with toDate(value), or using the "Quick Resolution" option to try automatic conversion, but recommended to use toDate(value, "dd/MM/yyyy") |
12/31/2012 (US style, month first - non ambiguous) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
02/01/2012 (European style, day first - ambiguous) | toDate(value, "dd/MM/yyyy") |
02/01/2012 (US style, month first - ambiguous) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
31 December 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
31st December 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
31st Dec 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
December 2012 | toDate(value, "MMMM yyyy") |
2012-12 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
12/2012 | toDate(value, "MM/yyyy") |
DateLastPackageIssue dates
One issue to note is that the toDate function will always give a full date (i.e. down to the granularity of a specific day), which means that when you convert less granular dates (e.g. "December 2012" or just "2012") the toDate format will interpret this as being the 1st of the month or year in question. This works well as a default behaviour for values in "DateFirstPackageIssue" but not for values in "DateLastPackageIssue". In the latter case some additional manipulation maybe needed if what is desired is to use the last day of the relevant month/year (which would be the usual interpretation for DateLastPackageIssue).
The examples below show how you can use the 'inc' function (to add or subtract units of time from a date) to achieve this.
Example date | Convert for DateFirstPackageIssue | Convert for DateLastPackageIssue |
---|---|---|
2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion (results in [date 2012-01-01T00:00:00Z]) | inc(inc(toDate(value),1,"year"),-1,"day") (results in [date 2012-12-31T00:00:00Z]) |
2012-12 | toDate(value), or use the "Quick Resolution" option to try automatic conversion (results in [date 2012-12-01T00:00:00Z]) | inc(inc(toDate(value),1,"month"),-1,"day") (results in [date 2012-12-31T00:00:00Z]) |