Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would really appreciate some help with standardising date formats from two different data sources, as part of my load script.
Format
Field1 dd/mm/yyyy hh:mm:ss
Field2 yyyymm
I would ideally like both formats to be changed to mmm-yy, but I am not sure how to write this as part of the load script.
Thanks in advance for any help.
Regards,
Daniel
Try to write like
Load
Date(Field1, 'MMM-YY') as Field1
Date(Field2, 'MMM-YY') as Field2
From Location;
Or
Load
Date(Date#(Field1, 'dd/mm/yyyy hh:mm:ss'),'MMM-YY') as Field1
Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as Field2
From Location;
date ( Field1 , 'MMM-YY' )
Try to write like
Load
Date(Field1, 'MMM-YY') as Field1
Date(Field2, 'MMM-YY') as Field2
From Location;
Or
Load
Date(Date#(Field1, 'dd/mm/yyyy hh:mm:ss'),'MMM-YY') as Field1
Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as Field2
From Location;
For Field1
Date(Field, 'MMM-YY')
For Field2
Date(Date#(Field2, 'YYYYMM'), 'MMM-YY')
Load
Date(Floor(Field1), 'MMM-YY') as Field1
Date(Date#(Field2, 'YYYYMM') 'MMM-YY') as Field2
From <>;
Assuming Field1 is being read as a date field already, otherwise parsing function date#() has to be used there too.
use the 'replace' function
ex:
u r date is like 20-03-2014
you want show like 20/03/2014
Date(replace(filedname,'-','/'),'DD/MM/YYYY')
case2:
Directly
Date(filedname,'DD/MM/YYYY') use this code for all the dates in the scripy
Hi,
Thank you for all the replies.
I used the function Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as Field2
Regards,
Daniel