Dear all,
I have an enquiry related to advanced mysql. I have a filter variable that is of "Date" data type, the field is not mandatory. So, we assume the variable will be "Null" when passed into the mysql query, upon checking the value before executing the mysql, it is "01-01-1990".
So in my mysql query,
( @AwardDateFrom IS NULL OR r.AwardDate >= @AwardDateFrom )
Do i put something like this ? It is not working as intended, data are not showing, not if the variable is "Null". Its showing when the variable is not "Null", which is a proper date.
In OutSystems O11, a Date/DateTime parameter is never database NULL when it reaches your Advanced SQL.
If the input is left empty, OutSystems sends the platform null date, which is 1900-01-01 (or 1900-01-01 00:00:00 for DateTime), not a SQL NULL. How to filter put record with default date
1900-01-01
1900-01-01 00:00:00
NULL
So this condition:
(@AwardDateFrom IS NULL OR r.AwardDate >= @AwardDateFrom)
will never have @AwardDateFrom IS NULL = TRUE, because @AwardDateFrom is actually 1900-01-01, not NULL.
@AwardDateFrom IS NULL
@AwardDateFrom
Treat the OutSystems null date as “no filter”, for example:
WHERE ( @AwardDateFrom = {NullDate} -- or literal '1900-01-01' for MySQL Date OR r.AwardDate >= @AwardDateFrom )
Typical patterns in MySQL Advanced SQL for OutSystems:
-- If AwardDateFrom is Date WHERE ( @AwardDateFrom = '1900-01-01' OR r.AwardDate >= @AwardDateFrom ) -- If AwardDateFrom is DateTime WHERE ( @AwardDateFrom = '1900-01-01 00:00:00' OR r.AwardDate >= @AwardDateFrom )
Alternatively, you can compare to NullDate() in an Aggregate, but inside Advanced SQL you have to compare to the actual literal value that OutSystems uses (1900-01-01...). How to filter put record with default date
NullDate()
1900-01-01...