How to handle nulldate() or no date values from input to database

Hi There

please suggest how to store null values (datetime column at database) instead of default null value "1900-01-01 00.00" into database.


Requirement: End_dt can be null in my application, if i use nulldate() or #1900-01-01 in input Textbox null value, database is recording date as "1900-01-01 00:00:00.000". 

it should be End_Dt Null at database


Thanks in advance.

-Phani


Hi Phani,

You can't store NULL values into an Outsystems Entity, only with an external Entity defined in an Extension.

Can you tell us what you're trying to achieve? Why can't you store that value and use it as a signifier for a NULL value?

I have explained my requirement in my original post.

I have one column End_dt and it can be null

when saving record into database through form, it is saving #1900-01-01 in Database.

but I want End_dt column should ne Null or nothing in database column value instead #1900-01-01 until I update it in future.


Thanks

-Phani


I read your original post again and I can't find why you need to do that. 

I don't think it's possible, so that's why I'm asking for more details. There may be an alternative way to accomplish what you need, but we will only be able to suggest alternatives if we understand the reasons behind you needing to save actual NULL values into your fields.

This is a glaring flaw in OS - it simply doesn't support nulls. The 'null' value for strings is actually "", the 'null' value for integers is actually 0, and the 'null' value for dates is actually 1900-01-01 00:00:00.000.

See a very heated discussion (active since 2010...) here: https://www.outsystems.com/ideas/213/null-values


For your case, you will either need to:

1) conditionally check the 'end_dt' and point your flow in the right direction if it's 1900-01-01 - this is fine as long as that date will never be a valid value for the 'end_dt' field

OR, if 1900-01-01 is a valid date that could actually exist:

2a) Change the 'null value' field to a date that isn't valid, then do option 1

OR

2b) Add an additional boolean column in the table called something like "HasEndDate" to act as a "IsNull" identifier for that property.

I don't know if I'd call it a flaw. It looks like it's a deliberate design decision. And plenty of complex projects have been built in Outsystems since 2010.

A deliberate design decision can still produce a flaw. You can factor it into your designs and work around it, but the fact that people need to come up with these workarounds to get basic things working as expected is pretty bad - and for a product that advertises itself as integrating easily with existing systems, no null value support can sure cause some headaches when you need to interact with other DBs.

But this isn't really the place to argue for or against it - the ideas thread has plenty of that already.


Phani M, MichaelR.

OutSystems does not support Null values in values stored in memory, however it does allow you to store NULL values on the database if you use SQL queries.

Please check my post which gives an example of how to save NULL decimal values on the database (and could easily be adapted for dates): https://www.outsystems.com/forums/discussion/33882/null-value-decimal/#Post184472

Hello

why are you saying it does not support null values? it is true that for OS it show as 1900-... but you can allways do


"date = @null" where @null is a "null" string  and expandinline  = "yes". if you do this you can get records when you ask sql where "date is null", it will return that date.

So this is a null value for external dbs