date

[satishqlik]

Hi All,

I have date field with diff date formats

and I've been trying to convert into one standard format.

But not working properly can any one suggest me on this?

LOAD [Call Id],

     Status,

     Odate,

     Date((Alt(

     Date#(Odate,'DD-MMM-YY'),

     Date#(Odate,'YYY-MMM-D'),

     Date#(Odate,'YYYY-MMM-DD'),

     Date#(Odate,'MMM/DD/YYYY'),

     Date#(Odate,'MMM/D/YYYY'),

     Date#(Odate,'YYYY-D-MMM'),

     Date#(Odate,'YYYY-DD-MMM'),

     Date#(Odate,'YY-MMM-DD'),'DD-MMM-YY')))as mynewdate

FROM

<>;

[Chanty4u]

try this

SET DateFormat='DD-MMM-YY';

LOAD [Call Id],

     Status,

     Odate,  

     Date(Date#(Odate,'YY-MMM-DD'),'DD-MMM-YY')))as mynewdate

[satishqlik]

Yes tried but still not working!

[Chanty4u]

den try below

Alt( Date#(Odate,'DD-MMM-YY'),

Date#(Odate,'M/D/YYYY'),

Date#(Odate,'D/M/YYYY'),

Date#(Odate,'YYYYMMDD'),

Date#(Odate,'DD.MM.YYYY'),

Date#(Odate,'YYYY-MM-DD')

) as Odate

or else

date(floor(alt(

Date#(Odate,'DD-MMM-YY'),

Date#(Odate,'M/D/YYYY'),

Date#(Odate,'D/M/YYYY'),

Date#(Odate,'YYYYMMDD'),

Date#(Odate,'DD.MM.YYYY'),

Date#(Odate,'YYYY-MM-DD')

)),  'DD/MM/YYYY') as Odate

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD [Call Id],

    Status,

    Odate,

    Date(Alt(Date(Odate),

    Date#(Odate,'DD-MMM-YY'),

    Date#(Trim(Odate),'M/D/YYYY'),

    Date#(Odate,'YYY-MMM-D'),

    Date#(Odate,'YYYY-MMM-DD'),

    Date#(Odate,'MMM/DD/YYYY'),

    Date#(Odate,'MMM/D/YYYY'),

    Date#(Odate,'YYYY-D-MMM'),

    Date#(Odate,'YYYY-DD-MMM'),

    Date#(Odate,'YY-MMM-DD')), 'DD-MMM-YY') as mynewdate

FROM

[..\..\..\Downloads\data.xlsx]

(ooxml, embedded labels, table is Sheet1);

[antoniotiman]

I think You forgot

DD-MMM-YYYY format in Alt() expression