Values in the following columns must be formatted as a Refine 'date':
...
| 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]) |
Seasons and Quarters as dates
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:
| Example date | Interpretation for DateFirstPackageIssue | Convert for DateFirstPackageIssue | Interpretation for DateLastPackageIssue | Convert for DateLastPackageIssue |
|---|---|---|---|---|
| Winter 2012 | 21st December 2011 | toDate(substring(value,length(value)-4) + "-12-21") | 20th March 2012 | toDate(substring(value,length(value)-4) + "-03-20") |
| Spring 2012 | 21st March 2012 | toDate(substring(value,length(value)-4) + "-03-21") | 20th June 2012 | toDate(substring(value,length(value)-4) + "-06-20") |
| Summer 2012 | 21st June 2012 | toDate(substring(value,length(value)-4) + "-06-21") | 20th September 2012 | toDate(substring(value,length(value)-4) + "-09-20") |
| Autumn 2012 | 21st September 2012 | toDate(substring(value,length(value)-4) + "-09-21") | 20th December 2012 | toDate(substring(value,length(value)-4) + "-12-20") |
| Fall 2012 | 21st September 2012 | toDate(substring(value,length(value)-4) + "-09-21") | 20th December 2012 | toDate(substring(value,length(value)-4) + "-12-20") |
| 1st Quarter 2012 | 1st January 2012 | toDate(substring(value,length(value)-4) + "-01-01") | 31st March 2012 | toDate(substring(value,length(value)-4) + "-03-31") |
| 2nd Quarter 2012 | 1st April 2012 | toDate(substring(value,length(value)-4) + "-04-01") | 30th June 2012 | toDate(substring(value,length(value)-4) + "-06-30") |
| 3rd Quarter 2012 | 1st July 2012 | toDate(substring(value,length(value)-4) + "-07-01") | 30th September 2012 | toDate(substring(value,length(value)-4) + "-09-30") |
| 4th Quarter 2012 | 1st October 2012 | toDate(substring(value,length(value)-4) + "-10-01") | 31st December 2012 | toDate(substring(value,length(value)-4) + "-12-31") |