Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: DATE CONVERSION

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
Highlighted
Not applicable

Re: DATE CONVERSION

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


SQL Server Helper - Tips and Tricks - Date Formats

Highlighted
Not applicable

Re: DATE CONVERSION

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

Highlighted
Not applicable

Re: DATE CONVERSION

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: DATE CONVERSION

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

Highlighted
Master II
Master II

Re: DATE CONVERSION

Try this

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

Highlighted
Not applicable

Re: DATE CONVERSION

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