topic Re: Date#() function: Worng type of output in QlikView

Date#() function: Worng type of output

Wed, 20 May 2015 12:06:01 GMT

Hi all,

I'm trying to convert a text date into its numeric value using the Date#(...) function but for some reason it is not working properly.

--------------------------------------------------------------------------

Here is the piece of code I'm using for testing:

--------------------------------------------------------------------------

LET vMinDateText = MakeDate(2015,03);

LET vMaxDateText = Today(1);

LET vMinDate = Date#('$(vMinDateText)','DD/MM/YYYY');

LET vMaxDate = Date#('$(vMaxDateText)','DD/MM/YYYY');

TRACE >>> vMinDateText: $(vMinDateText);

TRACE >>> vMaxDateText: $(vMaxDateText);

TRACE >>> vMinDate: $(vMinDate);

TRACE >>> 'vMaxDate: $(vMaxDate);

--------------------------------------------------------------------------------------

The output displayed in the running script window is:

--------------------------------------------------------------------------------------

>>> vMinDateText: 1/03/2015

>>> vMaxDateText: 20/05/2015

>>> vMinDate: 1/03/2015

>>> vMaxDate: 20/05/2015

As you can see the data type stays the same. vMinDate and vMaxDate are texts instead of numbers.

Any idea on what I have been doing wrong ?

Thanks a lot for your help

Re: Date#() function: Worng type of output

Gysbert_Wassenaar — Wed, 20 May 2015 18:03:43 GMT

The Date# function merely turns a string into a date. It gets the default date format of your document unless you use the date() function to specify a format explicitly. If you want a number then use a function that returns a number (like the num function) on it: LET vMinDate = num(Date#('$(vMinDateText)','DD/MM/YYYY'));

Re: Date#() function: Worng type of output

Colin-Albert — Wed, 20 May 2015 18:09:08 GMT

Date#() formats input text strings into a dual date value.

To format the output date you need to use Date() to convert the output value to the required format.

This post by Henric Cronstrom hic‌ has a clear explanation on formatting dates for input and output Get the Dates Right

Re: Date#() function: Worng type of output

Thu, 21 May 2015 09:11:15 GMT

Thanks a lot Gysbert.

I indeed needed a number so I used the num fonction as you advised and it is working fine now.

Re: Date#() function: Worng type of output

Thu, 21 May 2015 09:18:03 GMT

Thanks for sharing the Get the dates Right post