Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
hkg_qlik
Contributor II

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
MVP
MVP

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.


6 Replies
Not applicable

Re: DATE CONVERSION

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


SQL Server Helper - Tips and Tricks - Date Formats

Not applicable

Re: DATE CONVERSION

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

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.

MVP
MVP

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.


er_mohit
Honored Contributor II

Re: DATE CONVERSION

Try this

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

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

Community Browser