From Date YYYYMDD as number to Real Date

1

Hi,

Some ERP extractions give date as number like 20161225 = YYYYMMDD
I would rather have a real date format like 25/12/2016

To cope with that, I use a “Calculate New Column” transformation
NewDate = date(
right(astext([DATE]),2)
& ‘/’ &
mid(astext([DATE),5,2)
& ‘/’ &
left(astext([DATE]),4)
,‘dd/MM/yyyy’)

Is there an other more efficient way ? (string calculation are slower than number calculation…)

Best regards
Michel

2

Hi Michel,

in EasyMorph, similarly to Excel, dates are numbers. Function date() converts text date into a number date. So if you need a number date then you can use

date(20161225, 'yyyyMMdd')

which returns 42729 that can be shown as date using the column formatting in the sidebar.

If you need a text date, then you can use format() which is opposite to date(). E.g.

format(date(20161225, 'yyyyMMdd'), 'dd/MM/yyyy') returns text string ‘25/12/2016’.

PS. Note that in text functions like left() or mid() 1st argument is automatically converted to text, no need to use astext().

3

Very effective ! Thanks a lot.