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")

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])

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")

Browser not supported