Getting Null date from SQL Server DB

Getting Null date from SQL Server DB

  
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

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.
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
Hi Justin,

Thanks a lot for your reply.

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

Ravi -

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

J.Ja
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.
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()

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
It can happen that the table already existed and a new attribute was added? This way the values would be NULL.
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
You are right. There are some cases where we will have NULL values, but not on DATE type attributes.
Justin James wrote:
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
 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...
 
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.
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.
 
if you can't update the external table, just use an advanced query to get the NULL values.