Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date format

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

3 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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.

swuehl
MVP
MVP

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.