topic Re: Convert yyyymmdd to dd/mm/yyyy in QlikView

Convert yyyymmdd to dd/mm/yyyy

Wed, 21 Oct 2015 08:47:51 GMT

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

Re: Convert yyyymmdd to dd/mm/yyyy

swuehl — Wed, 21 Oct 2015 09:00:10 GMT

Your parsing format code should be

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

Re: Convert yyyymmdd to dd/mm/yyyy

HirisH_V7 — Wed, 21 Oct 2015 09:05:05 GMT

Hi,

Try this

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

Thanks,

Hirish

Re: Convert yyyymmdd to dd/mm/yyyy

raman_rastogi — Wed, 21 Oct 2015 09:05:28 GMT

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

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

Re: Convert yyyymmdd to dd/mm/yyyy

sujeetsingh — Wed, 21 Oct 2015 09:06:08 GMT

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

Re: Convert yyyymmdd to dd/mm/yyyy

Wed, 21 Oct 2015 09:08:14 GMT

Thanks swuehl thanks everyone for your help.

It is working fine now.

Cheers,

Hasvine

Re: Convert yyyymmdd to dd/mm/yyyy

jonathandienst — Wed, 21 Oct 2015 09:08:53 GMT

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.

Re: Convert yyyymmdd to dd/mm/yyyy

quriouss — Wed, 21 Oct 2015 11:51:34 GMT

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).