I want to check if a date field in a left join aggregate is null.
I have 2 tables, Task and Completion, and an aggregate that shows all records in the Task table and the CompletionDateTime column in the Completion table via a left join. If there is no entry for the task in the Completion table, of course the CompletionDateTime field is null, and is displayed in Service Studio as 1900-01-01 00:00:00 . However if I set a conditional to compare it with NullDate() it *always* evaluates to false. How do i get True if the field is null (or zero, or whatever Outsystems substitutes Null with in a date field)?
First, ignore any advise above that mentions DateTimeToDate(). A Date Time variable can be compared to NullDate() and this will return True if it's 1900-01-01 00:00:00. No need to cast it.
That said, the difference between OutSystems Null values and database NULL values is biting you here. You should check the Executed SQL property (I can't, that only works after publishing) to see what SQL is generated, but I think any comparison is done only in case the value is not NULL (since otherwise the result is undefined). For your specific use case, instead of checking against NullDate() of Completion.CompletionDateTime, check for NullIdentifier() of Completion.Id. This amounts to the same (since you want CurrDate() if there's no Completion record) and is guaranteed to work since NullIdentifier() translates to an actual NULL value in the database.
Hey @Ken Wallace ,
Have you tried using the function DateTimeToDate() with your aggregate value and comparing that with the NullDate()?Cheers
Yes, I think I've tried every combination of those with the same result.
I'm sorry, for some reason I can't publish your module in my environment, so I can't create data to test options and be more helpful :/ But the built-in function should to the work
Regards
Hi Ken,
create a new attribute and in the formula, use (DateTimeToDate(CompletionDateTime)). Use this attribute for all checks like nulldate or other. If this not works, try to convert the date to text and make some text comparisons as well.
Thanks Kilian, that works fine now. I knew there must be a more elegant solution! FTR here's a .oml with the fix applied.
Happy to help Ken. The takeaway is to be careful when using values in filters and new columns when they could be NULL coming from the database.
Hey all,
This forum post seems to have some visibility in search engines on "How to filter sql null dates in aggregate", null dates in this case being actual database NULLs from a table in an External Database instead of the OutSystems NullDate() value, which yields to 1900-01-01.
We came up with the following trick that seems to work well in this case.
DateToText([YourDateFieldWithDbNulls]) <> NullTextIdentifier() and [YourDateFieldWithDbNulls] <> NullDate()
The filter above in an aggregate generates in SQL Server something similar to the following:
[YourDateFieldWithDbNulls] IS NOT NULL AND [YourDateFieldWithDbNulls] <> '1900-01-01'
So that covers both database NULLs and 1900's.