Aggregate filter - testing time values

Aggregate filter - testing time values

  

Hi,


I encounter strange behaviour when testing time values in a filter. When I use:

Beschikbaar.MA_van <= TextToTime("09:00:00") and Beschikbaar.MA_tot >= TextToTime("13:00:00")

I got a correct result (1 record). When I use:

Beschikbaar.MA_van <= Session.WorkspotSearch_MA_van and Beschikbaar.MA_tot >= Session.WorkspotSearch_MA_tot 

with testvalues #09\:00\:00# and #13\:00\:00#

I got 0 records (which is a false result). I cannot figure out if it's a bug or if I'm doing something wrong.  


Regards, Harry

NB: The testvalues are without the '\' sign. After posting I noticed that they where added in the post.


Regards, Harry

Hello Harry,

Could you paste here the executed sql?

You can open the aggregate, than in the breadcrumbs at the top right corner of the canvas, click in the name of the aggregate and the properties will appear, with this one (SQL).

Cheers,

Eduardo Jauch

Hi Eduardo,

The exceuted SQL where clause is

WHERE ((ENBeschikbaar.[MA_VAN] <= @WorkspotSearch_MA_van) AND (ENBeschikbaar.[MA_TOT] >= @WorkspotSearch_MA_tot))

If I use the 'hardcoded' filter (that gives a corect result): Beschikbaar.MA_van <= TextToTime("09:00:00") and Beschikbaar.MA_tot >= TextToTime("13:00:00")

then the executed SQL where clause is:

WHERE ((ENBeschikbaar.[MA_VAN] <= (convert(datetime, ('1900-01-01 ' + N'09:00:00'), 120))) AND (ENBeschikbaar.[MA_TOT] >= (convert(datetime, ('1900-01-01 ' + N'18:00:00'), 120))))

Regards, Harry




Triggered by this I tried:

Beschikbaar.MA_van <= DateTimeToTime(Session.WorkspotSearch_MA_van) and Beschikbaar.MA_tot >= DateTimeToTime(Session.WorkspotSearch_MA_tot)

and this seems to give the wright result!!! This is converted to SQL:

WHERE ((ENBeschikbaar.[MA_VAN] <= (convert(datetime, substring(convert(varchar(19), @WorkspotSearch_MA_van, 120), 12, 8)))) AND (ENBeschikbaar.[MA_TOT] >= (convert(datetime, substring(convert(varchar(19), @WorkspotSearch_MA_tot, 120), 12, 8)))))


I don't know however why I should have to do this. Should not Outsystems platform do this for me (attributes and paraamters are of type time)?

Regards, Harry


Hi Harry,

What are the types of the entity attributes and of the session variables?

Cheers,
Eduardo Jauch

Eduardo,


both of type TIME


Regards, Harry

Solution

Hello,


Just a remark.
When assigning literal values to a TIME variable (like the session), it is not enough to give it something like this: #09\:00\:00#. You need to add the date also, like it was a NULL date: "#1900-01-01 09:00:00#"

I think this is probably because the TIME data type is defined inn the database as a "DateTime", not as a "Time".
If you don't "convert it" to a DateTime, when the SQL is compared, in the aggregate, it will always return False.

I think the Aggregate should handle this, but clearly it does not.

Men... stop finding bugs! xD
Just kidding. I don't know if this is a bug or just some design decision.

Cheers,
Eduardo Jauch

Solution

You can find here how the data types are defined in database.
As you can see, all date and time types are converted to timestamp.

>>You can find here how the data types are defined in database.

Thnx!

>>Men... stop finding bugs! xD

Doing my best  


Thank you for all the help!

Harry

One question though. It seems that when I leave the filterarguments for textattributes empty all records are selected (no where clause) but when I leave the filterarguments for time fields empty no records are selected (the where clause consists the time filter). I don't know why, any idea (a bug  ?) ?

Regards, Harry   

Hi Harry :)

No errors here  ;)
This is the expected behaviour.

Also, I did some tests, and it seems that the only place where the error we saw happens is when we use the format #09\:00\:00# in the Test values. 

Cheers,
Eduardo Jauch

Hi,

Ok. I think I know what went wrong. Sometimes the filter edit boxes 'holds' old input. I first deleted the testfilter input in the Name field, Then I deleted that (selected and CTRL x), and then I edited the time box. Then sometimes (so not all the time) the edit box for Name is filled automaticly again. Seems a little bug. Maybe a clear filter button would be nice.

Regards, Harry