Date format

[#1]

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

[swuehl · Accepted solution]

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

[#3]

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]

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.