topic Date format in QlikView

Date format

Tue, 16 Oct 2012 13:11:20 GMT

When I format my last modified date in my QVW, why does it appear like the sample attached?

Re: Date format

swuehl — Tue, 16 Oct 2012 14:02:32 GMT

Because QV is interpreting the number 20121012 as days since Dec 30, 1899.

You need to tell QV that it should be interpeted as date, and the special format:

=Date#(20121012,'YYYYMMDD')

This will only change the numerical part of the value, but would still display 20121012. But applying a date function like year() or just date() will return correct results:

=Date(Date#(20121012,'YYYYMMDD'))

So just use

Date(Date#(LASTMODFIELD,'YYYYMMDD')) as LASTMODFIELD

in your script.

Hope this helps,

Stefan

Re: Date format

Tue, 16 Oct 2012 21:04:25 GMT

I had already done this in the script. But when I format the date in a straight table to MM/DD/YYYY it returns that random number for YYYY like in my sample.

I may have to go back to the SQL that generates the QVD that im reading and reformat the date there.

Re: Date format

swuehl — Tue, 16 Oct 2012 21:40:04 GMT

Well, as I said, it's not a random number, the Date is correct for a value of 20121012.

I assume at any point in your load script(s) the incoming Date is formatted with format code 'YYYYMMDD', and either a pure text value (so QV just interpretes the Date as a number) or somewhere /somehow the numerical part of the Date field value gets replaced by the textual representation interpreted as number.

I would also suggest going back to the code that reads the Date in from the source, then check that the value is correctly read in as Date and trace its transformation and load into your final application.