How to filter put record with default date

How to filter put record with default date

  

Hi,

I learn that OutSystems will create default date of "1900-01-01 00:00:00" if the DateTime Column is empty.

How do i filter out those record with the default date in SQL widgets?


I try below but not working

Does anyone has idea on this?Help!!

Solution

I think it is a null. Try a nulldate function. There is no null in OS. Null date in OS is 1900-01-01

Solution

Hi Johnson,

I agreed with Eric you should use Nulldate function in Outsystems Nulldate is  "1900-01-01 00:00:00".

Regards

-SK-

Hi,

To summarize about NULL:

1. in aggregate filter, use function NullDate()

2. in PL/SQL widget, use IS NOT NULL (say it Oracle db)

3. in external db aggregate, use something like: ENTITY.DATE + "" + NullDate() = NullDate() [https://www.outsystems.com/forums/discussion/14616/how-to-handle-nulls-with-outsystems-and-dates-before-01-01-1900/]

4. to migrate from Outsystems entity (table) to Oracle DB, use something like this:

CASE
                      WHEN TO_DATE ('1/1/1900', 'DD/MM/YYYY') =
                              CAST (
                                 os_table_name.COLL_BIRTH_DATE AS DATE)
                      THEN
                         NULL
                      ELSE
                         CAST (os_table_name.COLL_BIRTH_DATE AS DATE)
                   END


regards,

Indra


Hi Johnson Lim,

I just tried filtering a SQL tool by date:

and it worked as expected:

Can you explain what where you trying to do?

indra budiantho wrote:

Hi,

To summarize about NULL:

1. in aggregate filter, use function NullDate()

2. in PL/SQL widget, use IS NOT NULL (say it Oracle db)

3. in external db aggregate, use something like: ENTITY.DATE + "" + NullDate() = NullDate() [https://www.outsystems.com/forums/discussion/14616/how-to-handle-nulls-with-outsystems-and-dates-before-01-01-1900/]

4. to migrate from Outsystems entity (table) to Oracle DB, use something like this:

CASE
                      WHEN TO_DATE ('1/1/1900', 'DD/MM/YYYY') =
                              CAST (
                                 os_table_name.COLL_BIRTH_DATE AS DATE)
                      THEN
                         NULL
                      ELSE
                         CAST (os_table_name.COLL_BIRTH_DATE AS DATE)
                   END


regards,

Indra


wow...