Date Format Calendar issue with Qlikview 10 not apparent in Qlikview 9

[#1]

We have an issue with a custom generated Calendar that now in Qlikview 10 is not pulling through the dates in the correct format.

I know there is a supposed fix for this in qlikview 10 sr1 but we are not in a position to upgrade at the moment so I was wondering if anyone could help re word our script to give us the date format we require.

In Qlikview 9 when pulling through a "work_day" field we would get the date in the following format

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

This format works perfect for us and is used to base calculations on for graphs etc.

Once upgraded to Qlikview 10 all our Graphs started to display the error "no data to display" even though the script had not been changed.

Digging further I found when pulling through the same "work_day" field in Qlikview 10 now gave me the date format as below

40379.000000

40378.000000

40234.000000

Which obviously is not right and is what was causing the issues with our graphs.

I have since re-written the script a little, so intsead of it just selecting the raw field, the select statement now says:

to_char(work_day,'yyyy-mm-dd' hh:mm:ss) Work_Day,

I reloaded the script thinking this would work and it has to a point, now the date field format is as below

2007-12-01 12:01:00

2007-12-01 12:02:00

2007-12-02 12:03:00

For some reason Qlikview 10 is now creating hours and minutes which should not be there, and were not present in QV9. This therefore is still causing the graphs data not to be displayed and all calculations on the date fields are not working at all.

Is there anyway (without upgrading for now) to change my script to show the date format as above but as it was in QV9 with the zerored out hours, minutes and seconds?. Ideally I want to rewrite my line of code to give me the date field in this format again.

2007-12-01 00:00:00

2007-12-02 00:00:00

2007-12-03 00:00:00

If anyone could help it would be much appreciated. I have seen other posts mentioned the 'floor' function but with my lack of coding knowledge I am not sure how to implement this correctly into my existing code.

thanks

Lee Mackreath

[#2]

Wow.  I fixed it.

I tried all of that, and it still didn't work.  However, the table has a ton of different stuff concatenated to it.  The initial creation of the table had

null() as DenomValue,

Then the first real value stuffed in it was a numeric.  I think that forced everything else put into that field to always be in a numeric format, regardless.

I changed the creation of the table to have:

'' as DenomValue,

And now the DATE( ) format is displaying correctly.  DenomValue happens to be a NullAsValue field, so there should be no difference internally between '' or null().

Thanks for helping me reevaluate the field.  I'm not sure if I'd have thought of this on my own.

Sally