How to compare against a NullDate()

Hello everyone! I'm having issues with comparing a date field with NullDate() in a table record. My formula is this: 

SyntaxEditor Code Snippet

HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = NullDate() or HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = NewDate(1900,1,1) or HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE + "" = NullDate() + "" or HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = "#01/01/1900#"

So, as you can see, i'm trying pretty much every possible situation. In the past, I've gotten past it with Date + "" = NullDate() + "", but it doesn't work here. I know it's a null value because in my actual database, it's a null value. When this formula gets the date 07/26/2019, it says false, which is correct, but then I check it against the null value in my database and I also get false. Can anyone help me?


Edit: I actually got it to work with a not(SentDate = NullDate()), and that gave the SentDates a true and the NullDate() a false and I created an Attribute with the new formula, then filtered when the Attribute was false. Extremely annoying work around, so if anyone could still shine some light on what's actually going on, that'd be appreciated

Hi Andrew, 

can you tell us what exactly we are looking at here ?  

Is this an expression or a filter in an aggregate or a part of an sql query ??


you should be able to use following, both in expressions as in aggregate filters


Person.DateOfBirth = NullDate()


so I don't get why your above expression doesn't work.  What do you mean when you say 'in a table record'.  Are you sure you are looking at the right row/column from your database, maybe set a breakpoint right before this expression and debug to see the actual value you are comparing.


good luck,

Dorine



Dorine Boudry wrote:

Hi Andrew, 

can you tell us what exactly we are looking at here ?  

Is this an expression or a filter in an aggregate or a part of an sql query ??


you should be able to use following, both in expressions as in aggregate filters


Person.DateOfBirth = NullDate()


so I don't get why your above expression doesn't work.  What do you mean when you say 'in a table record'.  Are you sure you are looking at the right row/column from your database, maybe set a breakpoint right before this expression and debug to see the actual value you are comparing.


good luck,

Dorine




The Snippet I provided is a filter for an aggregate. By table record I meant I was using a Tables Records widget to display it all. And I did use breakpoints within a for loop to check as it cycled through and double checked everything, from correct column/row to data types. The frustrating this is when I included a 'not' before the comparison, the result would change depending on the SentDate. 

Hi Andrew,

I'm not too sure on what is the expected result of your Aggregate. Are you saying that with 

HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = NullDate()

as a filter, you're not receiving all the records you were expecting? Could you share some screenshots of your Aggregate and the incorrect data you're seeing?

HI Andrew,

If the column to be compared is date then this should work :

HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = NullDate()


If the column is datetime, then you may try this one :

DateTimeToDate(HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE ) = NullDate()



Hello Andrew,

Could you help me understand the context you are trying to do this?

HrPtoRequest is an OutSystems entity or is an external entity (imported using Integration Studio)?

If it is an OutSystems entity and the attribute is of DATE data type, this should work: 

HrPtoRequest.PTOR_SENT_TO_PAYROLL_DATE = NullDate()

But if the HrPtoRequest is on the right side of a LEFT JOIN, this may fail if there is no correspondence between it and the other entity.

For example, take these two entities:

Customers entity is EMPTY, and there is one record (Company A) in the Company entity:


If I do an aggregate like this:

With this filter:

I'll get NO results at all. The reason is in the way the platform works. As the platform does not stores NULL in fields when creating a record, but instead it stores a default value or the data type default value when a user value is not provided, and in the case of Date attributes this is #1900-01-01#, the platform is not expecting to find a NULL value.

So, this is the executed SQL:


SELECT TOP (32) ENCompany.[ID] o0, ENCompany.[NAME] o1, ENCustomers.[ID] o2, ENCustomers.[COMPANYID] o3, ENCustomers.[NAME] o4, ENCustomers.[BIRTHDATE] o5, ENCustomers.[LASTACCESSDATETIME] o6
FROM ([PTVKO2012].DBO.[OSUSR_1PU_COMPANY] ENCompany
Left JOIN [PTVKO2012].DBO.[OSUSR_1PU_CUSTOMERS] ENCustomers ON (ENCustomers.[COMPANYID] = ENCompany.[ID]))

WHERE (ENCustomers.[BIRTHDATE] = (convert(datetime, '1900-01-01')))

Notice that it is comparing against 1900-01-01, not against NULL.
But in a LEFT JOIN without a matching, during the query execution, the right side entity WILL have all its attributes set to NULL.

And thus, this filter will fail for those lines, and this is the exact opposite behaviour you would expect.

In this case, the workaround is to use the ID of the entity as well:

If you are using an EXTERNAL entity and you REALLY have NULL dates in your database, then you probably will want to set the Default Value Behaviour property of the attribute to Convert To/From NULL value in the Database. https://www.outsystems.com/help/integrationstudio/9.0/Managing_Extensions/Entity_Properties.htm

But I don't know if this works also for the filter. If not, then in the filter you can do something like this:

Customers.BirthDate + "" + NullDate() = "" + NullDate() or Customers.Id = NullIdentifier()

This way, the query executed in the database will be:

SELECT TOP (32) ENCompany.[ID] o0, ENCompany.[NAME] o1, ENCustomers.[ID] o2, ENCustomers.[COMPANYID] o3, ENCustomers.[NAME] o4, ENCustomers.[BIRTHDATE] o5, ENCustomers.[LASTACCESSDATETIME] o6
FROM ([PTVKO2012].DBO.[OSUSR_1PU_COMPANY] ENCompany
Left JOIN [PTVKO2012].DBO.[OSUSR_1PU_CUSTOMERS] ENCustomers ON (ENCustomers.[COMPANYID] = ENCompany.[ID]))
WHERE ((((convert(varchar(10), ENCustomers.[BIRTHDATE], 120) + N'') + convert(varchar(10), (convert(datetime, '1900-01-01')), 120)) = (N'' + convert(varchar(10), (convert(datetime, '1900-01-01')), 120))) OR (ENCustomers.[ID] IS NULL))

As you can see, this will take care of comparing against a possible NULL value (in a kind of convoluted way, unfortunately). But it should work...

Hope this helps.