461
Views
9
Comments
How to compare against a NullDate()
Question

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

Rank: #140

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



mvp_badge
MVP
Rank: #39

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?

Rank: #6039

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()



mvp_badge
MVP
Rank: #5

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.

Rank: #33769

Try 


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



Rank: #8357

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.

mvp_badge
MVP
Rank: #5

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.