Issue with dates

[#1]

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?

[Anonymous]

Try this in table 3:

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

[#3]

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!

[muthukumar77]

Hi,

Can you send your data model?

[#5 · Accepted solution]

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.