Alt Doubt

[#1]

Hi,

I need your help on my script to change the date formate to one standard format

my script is looks like as below

temp2:

LOAD id,

     ord,

     Date(Alt(

     Date(ord),

     Date#(ord,'DD/M/YYYY'),

     Date#(ord,'DD/MM/YYYY'),

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

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

     'DD-MMM-YYYY') as desiredOP,

      sals

FROM

D:<\path>abc.xlsx

(ooxml, embedded labels, table is Sheet1);

I haven't get my desiredOP

PFB for your reference.

Thanks in adv!

[sunny_talwar]

Not sure which version of Excel you have, but this is what I have:

[#3]

Yes Sunny I got it it showing as Wednesday,october 05, 2016 .

But I wanted to know why it didn't recognise 10/5/2016 as 10-May-2016?

[sunny_talwar]

I guess store it as 5/10/2016 in your Excel because Excel's inbuilt format is MM/DD/YYYY instead of DD/MM/YYYY

[#5]

Only problem with 10/5/2016

It works fine for    29/2/2016 and showing 29-Feb-2016

So then how do we change? Do you have any solution for this?

[sunny_talwar]

I told you, you will have to fix your dates in the Excel file

[#7]

Only problem with 10/5/2016

It works fine for    29/2/2016 and showing 29-Feb-2016

I don't know where I went wrong exactly!

[sunny_talwar]

Anywhere you have 12/12 or 11/11 where the first thing can be Month or date, it won't probably read it correctly

[nagarjuna_kotha]

Hi Try this

LOAD id,

     DAte(Date#(ord),'DD.MMM.YYYY') as Date,

     Date(alt(date#(ord, 'YYYY-MM-DD'),date#(ord, 'MMM-DD-YYYY'),date(ord, 'M/D/YYYY'), date#(ord, 'DD-MM-YYYY'), date#(ord, 'DD/MM/YYYY'), 'No Date Found'),'DD.MM.YYYY') AS New,

     sals,

     desiredOP

FROM

(ooxml, embedded labels, table is Sheet1);

I hope this may helps you.

-Nagarjun