Dates

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 dateConvert 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 2012toDate(value), or use the "Quick Resolution" option to try automatic conversion
31st December 2012toDate(value), or use the "Quick Resolution" option to try automatic conversion
31st Dec 2012toDate(value), or use the "Quick Resolution" option to try automatic conversion
December 2012toDate(value, "MMMM yyyy")
2012-12toDate(value), or use the "Quick Resolution" option to try automatic conversion
12/2012toDate(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 dateConvert for DateFirstPackageIssueConvert for DateLastPackageIssue
2012toDate(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-12toDate(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 dateInterpretation for DateFirstPackageIssueConvert for DateFirstPackageIssueInterpretation for DateLastPackageIssueConvert for DateLastPackageIssue
Winter 201221st December 2011toDate(substring(value,length(value)-4) + "-12-21")20th March 2012toDate(substring(value,length(value)-4) + "-03-20")
Spring 201221st March 2012toDate(substring(value,length(value)-4) + "-03-21")20th June 2012toDate(substring(value,length(value)-4) + "-06-20")
Summer 201221st June 2012toDate(substring(value,length(value)-4) + "-06-21")20th September 2012toDate(substring(value,length(value)-4) + "-09-20")
Autumn 201221st September 2012toDate(substring(value,length(value)-4) + "-09-21")20th December 2012toDate(substring(value,length(value)-4) + "-12-20")
Fall 201221st September 2012toDate(substring(value,length(value)-4) + "-09-21")20th December 2012toDate(substring(value,length(value)-4) + "-12-20")
1st Quarter 20121st January 2012toDate(substring(value,length(value)-4) + "-01-01")31st March 2012toDate(substring(value,length(value)-4) + "-03-31")
2nd Quarter 20121st April 2012toDate(substring(value,length(value)-4) + "-04-01")30th June 2012toDate(substring(value,length(value)-4) + "-06-30")
3rd Quarter 20121st July 2012toDate(substring(value,length(value)-4) + "-07-01")30th September 2012toDate(substring(value,length(value)-4) + "-09-30")
4th Quarter 20121st October 2012toDate(substring(value,length(value)-4) + "-10-01")31st December 2012toDate(substring(value,length(value)-4) + "-12-31")