topic Re: DATE CONVERSION in QlikView

DATE CONVERSION

hkg_qlik — Wed, 20 Nov 2013 22:48:32 GMT

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,

Re: DATE CONVERSION

Thu, 21 Nov 2013 00:06:14 GMT

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

SQL Server Helper - Tips and Tricks - Date Formats

Re: DATE CONVERSION

Thu, 21 Nov 2013 00:06:36 GMT

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

Re: DATE CONVERSION

Thu, 21 Nov 2013 00:16:31 GMT

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

Re: DATE CONVERSION

jagan — Thu, 21 Nov 2013 02:01:00 GMT

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.

Re: DATE CONVERSION

er_mohit — Thu, 21 Nov 2013 04:21:24 GMT

Try this

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

Re: DATE CONVERSION

Thu, 21 Nov 2013 05:22:24 GMT

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