topic Re: Issue with dates in QlikView

Issue with dates

Wed, 25 Nov 2020 16:16:04 GMT

Hello fellow Qlikviewers,

I'm having some trouble with one of my dates within the loading script

Mu quest is a search for the max of dates of 3 tables.

I'm from the Netherlands so that means we normally use a dateformat as DD-MM-YYYY.

Apparently my Database cannot handle that input.

So after a lot of trial and error i noticed that MM-DD-YYYY was working well.

I'm using the dateformat: SET DateFormat='MM-DD-YYYY';

All Max dates are loaded as: Load DATE(max(datefield)) as MaxDate

Now my issue:

In table 1 i get a correct date for today: 16-12-2016

In table 2 i also get the same result: 16-12-2016

However, in table 3, my date is shown as: 05-31-2047

I'm using the exact same date format, the same database and the same load script..

At this moment i am Clueless!

Anyone has a bright idea?

Re: Issue with dates

Anonymous — Fri, 16 Dec 2016 16:20:36 GMT

Try this in table 3:

date(max(datefield),'MM-DD-YYYY') AS MaxDate

Re: Issue with dates

Wed, 21 Dec 2016 11:17:04 GMT

Thanks, it was not the specific solution to fix my problem but it turned me in a direction where i found the problem at hand!

Re: Issue with dates

muthukumar77 — Wed, 21 Dec 2016 11:54:49 GMT

Hi,

Can you send your data model?

Re: Issue with dates

Wed, 21 Dec 2016 12:48:22 GMT

Hi Muthukumar,

My problem is not relevant anymore.

The string Marcio posted helped me resolve the problem at hand.

When using this string and mixing up the dates a bit MM-DD // DD-MM // D-M // M-D i noticed that the output did not change at all.

This problem was created by a user who had made some sales lines in 2007 but put in the date 31-05-2047 instead of 31-5-2007. This wrong date has never been changed and now it is stuck in our database because it is historic data.

I've joined this table with another one and used that (correct) date. Issue within Qlikview solved.