Convert yyyymmdd to dd/mm/yyyy

[#1]

Hello All,

I have an issue while converting a date from 'yyyymmdd' to 'DD/MM/YYYY'.

I am using the below formula in my Load script:

Date(alt(Date#(DATE, 'yyyymmdd')), 'DD/MM/YYYY') as Date

It gives me the correct answer in my table on qlikview.

Ex. 21/10/2015

However when I export the table in excel it becomes like this:

21//2015.

Also, when i try using the expression =if(Date= today(), Date) to get the data of only today, my table becomes empty.

Any idea why?

Many Thanks,

Hasvine

[swuehl]

Your parsing format code should be

Date(alt(Date#(DATE, 'YYYYMMDD')), 'DD/MM/YYYY') as Date

[HirisH_V7]

Hi,

Try this

= Date(date#(Date,'YYYY-DD-MM'),'MM/DD/YYYY')

Thanks,

Hirish

[raman_rastogi]

correct  'yyyymmdd' to 'yyyyMMdd' or 'YYYYMMDD'

=Date(alt(Date#(DATE, 'yyyyMMdd')), 'DD/MM/YYYY') as Date

[sujeetsingh · Accepted solution]

Date(alt(Date#(DATE, 'YYYYMMDD')), 'DD/MM/YYYY') as NewDate

[#6]

Thanks swuehl thanks everyone for your help.

It is working fine now.

Cheers,

Hasvine

[jonathandienst]

Swuehl has answered your question.

The alt() in your expression is redundant, as you have not provided an alternate value if the Date#() evaluates to null. It does not break the expression though.

[quriouss]

Just to note the reason for this (I fell into the same trap for a time);

'mm' is the internal format code for minutes, 'MM' is for Months.  (There is no difference between d and D, so Qlik 'knows' what to do in that case).