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

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.

Try 

(ENCustomers.[BIRTHDATE] <> NullDate()) = False

I use the below expression in Aggreate filter and it is working with SQL Server.

DateTimeToText(datetime_field) = NullTextIdentifier()

However, i dont like it. The datetime handling in OS is poor.

Hello George, 

I think both your solutions are wrong, at least for a normal OutSystems entity (no matter if you're using SQL or Aggregates) 

NullTextIdentifier is an empty string. So, 

DateTimeToText(datetime_field) = NullTextIdentifier()

should never return true. 

And NullIdentifier is 0 (zero). So, 

Year(datetime_field) = NullIdentifier()

should never return true. 

Even if the attribute is in the right side of a left join and the record in empty (no matching), converting NULL to a text or applying the Year over it will never have the expected result. 

"and in the case of Date attributes this is #1900-01-01#, the platform is not expecting to find a NULL value"

This is a BIG problem.

1900-01-01 is a valid date.

What if you have a business entity that has an "opened" and "closed" date?

Filtering for open businesses would always return no results, because the ABSOLUTELY NORMAL thing to do is filter on closed = null.

Now what if I have a table of businesses and there are a bunch of them that really closed in 1900?

Do I look for the ones with a null date?

I'm loving the software so far but this is just a complete CF. How are you supposed to deal in historical dates without collision into null references?

Date Field  <> NullDate() = False