GREL, date transformation

[Bernd Oswald]

I am not too familiar with GREL: can anybody please tell me what expression to use to transform "01986-03-29-000029 March 1986" into a normal date? thx!

[Owen Stephens]

The basics are:

This looks like two representations of the same date string together - I'm guessing the first part has the format:

yyyyy-MM-dd-hhmm

The second part of more obviously

dd MMM yyyy

This is a slightly unusual format although a Google suggests that some Wikipedia dates come through this way?

Anyway, it depends how you want the date formatted at the end of the day:

For 'yyyy-MM-dd' format: slice(value,1,11) 

(this simply extracts the date in this format from the string. Similarly:

For 'dd MMM yyyy' format: slice(value,16)

(this simply extracts the date from the latter part of the string

To manipulate the date, you can first extract a string as above, then convert to a Refine 'date' type, and convert back to a string (if necessary) with the relevant format specified. E.g.:

toDate(slice(value,0,11))

Will have the date as a date. You can use 'toString' to output as a formatted text string - e.g.:

toString(value,'dd/MM/yyyy') gives the standard UK date format

To put this together - from the string given you can get "29/03/1986" with the following one-liner

toString(toDate(slice(value,0,11)),'dd/MM/yyyy')

Owen