Values in the following columns must be formatted as a Refine 'date':
- DateFirstPackageIssue
- DateLastPackageIssue
How to format common dates into a correct GOKb date
In a Refine project if you have a DateFirstPackageIssue or DateLastPackageIssue in a standard format, in many cases you can convert this to a Refine date simply by using the 'toDate' function. This is also available as a 'Quick Resolution' offered for Errors on these columns as 'attempt automatic conversion'. There are a few scenarios where it is necessary to specify the date format for the 'toDate' function.
Note that specifically special care needs to be taken with dates in the European style 'dd/MM/yyyy' where the date could be interpreted as either a European style or US style date (the automatic conversion opts for the US style interpretation). While non-ambiguous European style dates are interpreted correctly, those that can be interpreted either way are intepreted as being in the US style. This means that for dates in the European style of dd/MM/yyyy it is always safer to treat the dates as ambiguous and specify the date format in the 'toDate' function as show in the table below.
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") |
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.
Example date | Convert for DateFirstPackageIssue | Convert for DateLastPackageIssue |
---|---|---|
2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion | inc(inc(toDate(value),1,"year"),-1,"day") |
2012-12 | toDate(value), or use the "Quick Resolution" option to try automatic conversion | inc(inc(toDate(value),1,"month"),-1,"day") |