Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have date format like this
9/10/2013
4-10-2014
this two format have in my data
can i load this data to script or do you what to modify data any one format before loading.
Hi
You can do it with something like this (change the field name DateField to match your data):
LOAD
Date(Alt(Date#(DateField, 'D/M/YYYY'), Date#(DateField, 'D-M-YYYY'))) As DateField
HTH
Jonathan
Use Alt function for this see the example for this
LOAD Date, Alt( date#( Date , 'DD/MM/YYYY' ),
date#( Date , 'DD-MM-YYYY' ),
) as NewDate;
LOAD * Inline
[
Date
9/10/2013
4-10-2014
];
see here (pag 😎 for detail
TIP 8: FIELDS WITH MIXED DATE FORMATS
If you have mixed date format in one field or you have data from different sources using
different formats, you can use the Alt() function to define several possible date formats:
Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),
Date#(MixDate,'M/D/YYYY'),
Date#(MixDate,'D/M/YYYY'),
Date#(MixDate,'YYYYMMDD'),
Date#(MixDate,'DD.MM.YYYY'),
Date#(MixDate'YYYY-MM-DD')
) as MixDate
The order of the interpretation functions also defines the priority when several formats are
possible for one specific field value, e.g. 8/4/2012, which in the United States means 4th of
August, but in the United Kingdom means 8th of April.