Values in the following columns must be formatted as a Refine 'date':
- DateFirstPackageIssue
- DateLastPackageIssue
Anchor common-dates common-dates
How to format common dates into a correct GOKb date
| common-dates | |
| common-dates |
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.
...
| 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") |
Anchor date-last-package-issue date-last-package-issue
DateLastPackageIssue dates
| date-last-package-issue | |
| date-last-package-issue |
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).
...
| 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]) |
| Anchor | ||||
|---|---|---|---|---|
|
Sometimes journal issue dates are given in the form of seasons or quarters rather than a specific day or month. These also need to be converted to a Refine Date. In order to have some consistency in interpreting dates the following guidelines and conversion suggestions are provided:
...