Date Format Issue - Date appears as '07/00/2016'

[farnsworth]

Hello all,  I'm getting the following issue with a date loaded in from a qvd.  The unformatted date (yesterday - 07/09/2016) appears as 07/00/2016.  If I go to properties -> Number and Override Document Settings and set to integer i get 42620 and if I set it as Date i get 07/09/2016.

This is fine but when the date is a dimension in a chart it shows as 07/00/2016 and also in the current selections it is the same.

So i need to change this in the script.

I have tried in the script to split out the date to the day month and year and put it back together using MakeDate but it still returns as 07/00/2016.

Day(Date) AS Day

,Num(Month(Date),'00') AS Month

,Year(Date) As Year

,MakeDate(Year(Date),Month(Date),Day(Date)) AS Date

[sunny_talwar]

May be try this:

MakeDate(Year(Date), Num(Month(Date)), Day(Date)) AS Date

[Anonymous]

It could be the dateformat set

SET DateFormat='DD/MM/YYYY';

[farnsworth · Accepted solution]

I resolved the issue on this. I have a download stage of the data which had formatting on the date.  The date was originally date time format and the time part is removed to leave just the date.

The only issue was at this stage the date format was set as 'dd/mm/yyyy' after the time section is dropped out.  So somehow the numeric date was kept but the format above should have been 'DD/MM/YYYY' as 'mm' returns the minutes in a date time hence the '00'.  Qlikview must do something behind the scenes which kept the numeric date format which is why further on in the process it displayed the numeric correctly but still displayed the date as 07/00/2016. 

So changing the date format back to 'DD/MM/YYYY' has done the trick.