Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Data Warehouse Table with the Calendar table having its date fields in the following format:
YYYYMMDD
Now I would like to convert it to MM/DD/YYYY.
I am currently using the following during my load:
Date(Date#(RECEIPT_DATE,'YYYYMMDD'),'MM/DD/YYYY') as %RECEIPT_DATE
But it's not giving me the right result.
Regards,
H
Hi Gandhi,
Date(Date#(RECEIPT_DATE,'YYYYMMDD'),'MM/DD/YYYY') as %RECEIPT_DATE -- This script definitely works if your date format is in this pattern, check the raw data in Qlikview without converting, how this data is loading in Qlikview. If it loads as number then you simply use
Date(RECEIPT_DATE, 'MM/DD/YYYY') as %RECEIPT_DATE
If this not works, can you post some sample values.
Regards,
Jagan.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
Please post some sample data so we can help. Your expression should work if the source field value like date.
Hi Gandhi,
Date(Date#(RECEIPT_DATE,'YYYYMMDD'),'MM/DD/YYYY') as %RECEIPT_DATE -- This script definitely works if your date format is in this pattern, check the raw data in Qlikview without converting, how this data is loading in Qlikview. If it loads as number then you simply use
Date(RECEIPT_DATE, 'MM/DD/YYYY') as %RECEIPT_DATE
If this not works, can you post some sample values.
Regards,
Jagan.
Try this
if(len(RECEIPT_DATE)>0,Date(Date#(RECEIPT_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as %RECEIPT_DATE
Hi,
First Change date format (which is mention in load script by default) according to your date format then try your date conversion script.
Regards,
Ashutosh