Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hkg_qlik
Creator III
Creator III

DATE CONVERSION

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.


View solution in original post

6 Replies
Not applicable

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]


SQL Server Helper - Tips and Tricks - Date Formats

Not applicable

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

Not applicable

Please post some sample data so we can help. Your expression should work if the source field value like date.

jagan
Luminary Alumni
Luminary Alumni

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.


er_mohit
Master II
Master II

Try this

if(len(RECEIPT_DATE)>0,Date(Date#(RECEIPT_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as %RECEIPT_DATE

Not applicable

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