Skip to Content (Press Enter)
UserImage.jpg
Johnson Lim
152Views
5Comments
Solved
How to filter put record with default date
Question

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!!

0
0
12 Jan 2018
Copy Link
2017-02-23 11-12-25
Eric Halim
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

0
0
12 Jan 2018
Copy Link
2025-04-22 05-54-18
Shashi Kant Shukla

Hi Johnson,

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

Regards

-SK-

0
0
12 Jan 2018
Copy Link
2018-01-19 03-18-31
indra budiantho

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


1
0
12 Jan 2018
Copy Link
2017-02-23 11-12-25
Eric Halim

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...


0
0
12 Jan 2018
Copy Link
2019-09-24 18-41-25
Jorge Martins
 
MVP

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?

0
0
12 Jan 2018
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.