Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Not sure which version of Excel you have, but this is what I have:
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?
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
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?
I told you, you will have to fix your dates in the Excel file
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!
Anywhere you have 12/12 or 11/11 where the first thing can be Month or date, it won't probably read it correctly
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