Date comparison in expression

[Anonymous]

Hi all,

I want to do a daily analisys so in the load script I have

LET TodayDate = today();

In the expression I compare with the field :

dtimport_formated that is obtained from dtimport in mysql load by:

CAST(dtimport AS DATE) as dtimport_formated

dtimport is an integer in the format : 20170710

the expression I use is

Sum({<dtimport_formated={$(TodayDate)}>}valor_brt)

it returns nothing, but...

if I use

LET TodayDate = date(today(),'YYYYMMDD');

and then compare it with

Sum({<dtimport={$(TodayDate)}>}valor_brt)

it works fine, but the date instead of showing dd/MM/YYYY is presented in YYYYMMdd

can anyone help ?

[tresesco]

Since they are different at places in format, once you have to use an extra formatting expression, either in set analysis or when you use the variable somewhere else. I.e. is if you get the variable with integer formatted, you can use date() while use variable somewhere like: =Date($(TodayDate), 'DD/MM/YYYY'); either way.

[Anonymous]

Hi, it almost works, but it converts 20170701 into 02/07/57125  instead of 01/07/2017

thanks

[Miguel_Angel_Baeyens]

It's the format mask

Date(Date#($(TodayDate), 'YYYYMMDD'))

[tresesco]

If you are using on integer value variable, try like:

= Date(Date#($(TodayDate), 'YYYYMMDD'), 'DD/MM/YYYY')

[Anonymous]

Nope, I' tried almost everything (if I've tried everything it would be solved) and still nothing.

the dates appera correct but they do not compare right

[Miguel_Angel_Baeyens]

Then the format in the fields and variables are different. Once they are the same, it will be fixed, but without a sample QVW, there is little else to check exactly where this difference resides.

As a side note, in the set analysis you also need to format the output to match with the field value format, for example:

Sum({< dtimport = {"$(=Date($(TodayDate), 'DD/MM/YYYY'))"} >} valor_brt)

Or even easier, use the numeric value instead of the date format in all fields. Also better performing.

[Anonymous]

Im using the numeric one right now, and it will suffice, but it really annoys me  not being able to do it the way I want.

I cannot send you the qvw because it contains company data.

[Miguel_Angel_Baeyens]

I figured as many of us handle sensitive data we cannot share, but without having a real app with real data, the troubleshooting is limited.

You could put side by side in a tablebox both fields, or in a text object the variable and on the other one value of the field, and see if, for example, one has time and the other only the date, how they display, etc.

[Anonymous]

the table was a good idea, found some stuff wrong, in the results,  Date($(TodayDate)) and $(TodayDate) present 30/12/1899 but htey appear correct in the header