topic Re: Date Format Issue - Date appears as '07/00/2016' in QlikView

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

farnsworth — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Thu, 08 Sep 2016 15:45:27 GMT

May be try this:

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

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

Anonymous — Thu, 08 Sep 2016 18:09:07 GMT

It could be the dateformat set

SET DateFormat='DD/MM/YYYY';

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

farnsworth — Fri, 16 Sep 2016 13:41:10 GMT

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.