Getting Null date from SQL Server DB

Ravi Vakkalanka

Question

Hi

I am trying to get all the records whose particular datetime column is null. I am using a simple query and using a join condition like this:

tablename.columnname = nulldate()

columnname is defined as Date Time -datatype in database and has lot of null values.

When I query the table in Outsystems the column name is filled with data like - "01/01/1900 00:00:00"
But I am not able to get the same data in a simple query when I use the above join condition.

Is this a defect in Outsystems or am I doing something wrong ? When I write a simple query in Database with a join condition like "COLUMNNAME IS NULL" it fetches all the records with null date values.

Any help in this regards is greatly appreciated.

Thanks
Ravi

04 Mar 2014

Ravi Vakkalanka

Pls let me know if anybody want to help but did not understand my question. I can explain it better :)

I need to know if this is a outsystems issue so that i will proceed writing the query in Advanced query. But only problem is we have many filters implemented in this screen and it will take some time to implement the filter in native way and also maintenance is tough for others.

05 Mar 2014

Justin James

MVP

Ravi -

"Under the hood", NULL dates in OutSystems are actually stored as "1900-01-01 00:00:00". So a simple query looking for NullDate() finds the rows where the data is 1/1/1900 and NOT "NULL" as you might expect, but in OutSystems they will appear to be blank/null.

J.Ja

05 Mar 2014

Ravi Vakkalanka

Hi Justin,

Thanks a lot for your reply.

So the solution for this problem is to use Advanced Query ?

05 Mar 2014

1 reply

10 Mar 2014

Show thread

Hide thread

João Batista

Staff

Ravi Vakkalanka wrote:

Hi Justin,

Thanks a lot for your reply.

So the solution for this problem is to use Advanced Query ?

Yes, basically if you want to get the "real" NULL date values, you need to do it in an advanced query just like you do it in SQL Server Management Studio.

If you need any help let me know.

10 Mar 2014

Justin James

MVP

Ravi -

It depends. What are you actually trying to get done?

J.Ja

09 Mar 2014

Ravi Vakkalanka

Thanks for your replies. I know if this is my only requirement in the front end to just get Null values then it might be better to use Advanced Query..But the screen I am working on has lot of filters (around 15) and it will be cumbersome to write a Adcanced Query with all these filters and IF and ELSE conditions like if the filter value is ALL, then ignore the WHERE Condition....

Hence I am going ahead with using SImple Query but with a small work around.....I am updating all the NULL values in DB to "01-01-1900 00:00:00" ...With this update ...now I am able to use the below condition and it fetches the results as expected:

tablename.columnname = nulldate()

10 Mar 2014

Justin James

MVP

Ravi -

If this table is managed by OutSystems, why do you have NULL values in date fields? Something is very wrong here.

Simply saying:

Entity.Attribute = NullDate()

should ALWAYS work. If you are seeing true NULL values in the DB, or if selecting NullDate() does not retrieve the values, you should contact Support.

J.Ja

11 Mar 2014

1 reply

13 Mar 2014

Show thread

Hide thread

Ravi Vakkalanka

Justin James wrote:

Hi Justin,

The tables are created in SQL Server and being used in Outsystems by publishing the schema extension into outsystems. External Interface (Informatica) polulates this SQL Server table with some NULL values into the DateTime Columns. So what I am trying to do is to access this table and wanted to get all the rows in the table whose DateTime Column have NULL values...

13 Mar 2014

João Batista

Staff

It can happen that the table already existed and a new attribute was added? This way the values would be NULL.

11 Mar 2014

Nuno Cavalheira Antunes

Hi João,

Actualy, if you insert the new attribute of type DATE with OutSystems, it should have a default of '01-01-1900 00:00:00', so even the old records should not be NULL.

Rgds,

NCA

12 Mar 2014

João Batista

Staff

You are right. There are some cases where we will have NULL values, but not on DATE type attributes.

13 Mar 2014

Nuno Reis

MVP

HI.

Can the insert be made in OutSystems NullDate format (or can a trigger change inserted dates to NullDate format)?
If the date isn't in OS null date, you have to create an advanced query.

14 Mar 2014

1 reply

14 Mar 2014

Show thread

Hide thread

Ravi Vakkalanka

Nuno Reis wrote:

HI.

Can the insert be made in OutSystems NullDate format (or can a trigger change inserted dates to NullDate format)?
If the date isn't in OS null date, you have to create an advanced query.

This is what i am doing to overcome this issue. I am using an advanced query to update the NULLs in Database to NULLDATE format.

14 Mar 2014

João Batista

Staff

if you can't update the external table, just use an advanced query to get the NULL values.

14 Mar 2014

Edward Aviza

Staff

While I realize this is a very old thread, I wanted to share that I can select rows from an external MS SQL Server DB that have a certain DateTime field as NULL by using this syntax in my aggregate's filter:

DateTimeToText(TableName.ColumnName) = NullTextIdentifier()

Hope this helps someone in the future! :-)

09 Mar 2023

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines