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!
See if this works:
Table:
LOAD id,
ord,
sals,
Date(Alt(
Date(ord),
Num(Date#(ord,'DD/M/YYYY')),
Num(Date#(ord,'DD/MM/YYYY')),
Num(Date#(ord,'DD-MMM-YY')),
Num(Date#(ord,'MMM-DD-YYYY'))),
'DD-MMM-YYYY') as desiredOP
FROM
Alt_Comunty.xlsx
(ooxml, embedded labels, table is Sheet1);
Thanks Sunny for quick response!
I tried as you suggested
but still am getting same output
please suggest
The desiredOP in the above image is not what you wanted?
In source It contains 10/5/2016 and expected output is 10-May-2016 but in my application It showing 5-Oct-2016
I don't know where I went wrong. Pls suggest
Your Excel doesn't have it right
Check what you have define here under 'Number'
No Sunny, In My excel it showing 10/5/2016 only!
But Once we loaded into qvw application It showing 05-Oct-2016
and also initially date format we have SET DateFormat='DD-MMM-YYYY';
10/05/2016 is interpreted as Oct-5th by your Excel. Can you try to convert that to long format in your Excel to check?
Sunny will you be elaborate little bit more?
How do we change ?
(Rightclik>Format cells> date/custom>?)