makedate function generates an integer as opposed to the actual date

[happyjoshua777]

Hello,

I used makedate(2099,12,31) expecting it to generate the actual date, at least according to:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...

Instead I kept getting '73050' which is an integer equiv. of 31/12/2099:

I had to resort to date(makedate(2099,12,31),'DD/MM/YYYY')

in order to end up with the expected date :

This is the command in QlikView:

if(len(date(EndDate,'DD/MM/YYYY')) < 1, date(makedate(2099,12,31),'DD/MM/YYYY'), date(EndDate,'DD/MM/YYYY')) as EndDate

Is makedate function behaving as expected? If, yes, does that mean that the offical article in help.qlik.com is outdated?

thanks

[adityaakshaya]

Hi Happy,

makedate function always return date . In your case, makedate is not behaving as expected.

Ca nyou share the application with some data, so that, I can see and let you know why are getting such results.

Regards,

Akshaya

[happyjoshua777]

Loading from Excel file column 'EndDate'

if employee if still working in the company then the respective field is empty

Otherwise there is there is a date of resignation, such as '

05-01-97

In QlikView I created Employee table with

...

    if(len(date(EndDate,'DD/MM/YYYY')) < 1, makedate(2099,12,31), date(EndDate,'DD/MM/YYYY')) as EndDate,

...

to generate EndDate column.

The end result is as per the first screen shot which is '73050' is getting generated by makedate()

Could this be due to:

    SET DateFormat='DD-MM-YY';

in the declaration section of my script?

I think this is related to the issue at hand:

In another qvw file for

    addyears(date(MyDate,'DD/MM/YYYY'),20)

The output is: 42856

which means I have to use date() for the second time as follows:

    date(addyears(date(MyDate,'DD/MM/YYYY'),20))

just to have a proper date value as my output 01/05/2016

I also have   SET DateFormat='DD-MM-YY';

declared in the beginning

     Note: MyDate is another column I was traversing in this second qvw

thanks

[balabhaskarqlik]

may be:

if(len(date#(EndDate,'DD/MM/YYYY')) > 0, makedate(2099,12,31), date(EndDate,'DD/MM/YYYY')) as EndDate.

[Anil_Babu_Samineni]

Can you post real values to test?

[happyjoshua777]

EndDate

05-01-97

31-12-97

Tried pasting the actual column from Excel. All other values in Excel rows are empty.

MyDate Excel column:

MyDate
08-12-48
19-02-52
30-08-63
19-09-37
04-03-55
02-07-63
29-05-60
09-01-58
27-01-66
30-08-63
29-05-60

[thevingo]

Hi

Can you check default date format

SET DateFormat='M/D/YYYY';

[Miguel_Angel_Baeyens · Accepted solution]

Yes, that is exactly what is happening.

QlikView uses by default, when creating a new document, the locale settings of the user session creating it. In my case, I have set Windows to English (US), but some of my colleagues have Swiss settings, so their date format is "DD.MM.YYYY"

The result of MakeDate() is correct in any case. It is worth mentioning that QlikView does not have dates as different data types: any value is susceptible to have a numeric representation and a literal representation. In the case of Date() related functions, day 1 (numeric) is 31/12/1899 (literal, using mask DD/MM/YYYY) and today is day 43285.

If the data is coming with a different format than the one in your environment variable DateFormat you will need to use, for the fields to be displayed in the front-end, functions like Date() with masks, otherwise the literal representation will not be possible and QlikView will revert to the numeric value.

You can also change that DateFormat variable, but then make sure that all the data you are loading is using the format you specify.

EDIT: the numeric values for dates, by the way, are underused by most of the developers I know. There is nothing wrong in using 43285 in functions to compare dates, get intervals, extract months, calculate age, etc. I only use Date() when I want to display a value, for which I always use the mask to avoid that, in case other developers or users open my application, they see a different value (4.7.2018 is April or July?)