Date conversion problem

[#1]

Dear all,

I have a problem with data conversion in an iterative process:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';


Let varMinDate = '1-1-2017'; 

Let varMaxDate = '10-1-2017'; 

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

The output is not what I have expected:

Even without single quotation mark still a problem, since I have let installed QV latest version in W10 environment.

Hope that you can explain me what went wrong.

Best regards,

Cornelis

[PrashantSangle]

Hi,

use

date(date#('1-1-2017','DD-MM-YYYY'),'DD-MM-YYYY') as

Let varMinDate = date(date#('1-1-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Let varMaxDate = date(date#('10-1-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Regards,

[#3]

Dear Prashant,

Thank you for your speedy response, but unfortunately it does not help.

Let varMinDate = date(date#('01-01-2017','DD-MM-YYYY'),'DD-MM-YYYY');

Let varMaxDate = date(date#('10-01-2017','DD-MM-YYYY'),'DD-MM-YYYY');

the output is still the same.

I have used 1-1-2017 and  01-01-2017 but both deliver the same results.

also changed D-M-YYYY instead of DD-MM-YYYY.

[sunny_talwar]

Try this:

Let varMinDate = Num(MakeDate(2017, 1, 1)); 

Let varMaxDate = Num(MakeDate(2017, 1, 10)); 

[sunny_talwar · Accepted solution]

Or even this

Let varMinDate = Num('1-1-2017');

Let varMaxDate = Num('10-1-2017');

[PrashantSangle]

Hi,

Try below script

Let varMinDate = num(date#(num#('1-1-2017'),'DD-MM-YYYY')); 

//exit Script;

Let varMaxDate = num(date#(num#('10-1-2017'),'DD-MM-YYYY')); 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

Regards,

Prashant

[#7]

Dear Sunny,

Thank you, this is the solution

I do not understand why the previous version works very well but not in a new W10 environment.

MakeDate and Num work both.

Best regards,

Cornelis

[sunny_talwar]

Not sure what the difference is... but from what I understand is that 1-1-2017 is expanded to -2017 within the dollar sign expansion (not read as date anymore) and hence a date from before 1900 (should be 2017 days before 01/01/1900)

[#9]

Yes, that is something that come up into my mind.

thanks!

[#10]

Hi Prashant,

Thank you for your valuable contribution.

This is also the solution, Sunny has just delivered a simple solution.

Nevertheless, your contribution is an interesting point for consideration.

Best regards,

Cornelis