How to save null value in datetime column

How to save null value in datetime column

  
Hi, 
   I am trying to save null in the datetime column if user doesn't enter anything but couldn't do it. I know it should be very simple but I couldn't figure it out.I tried changing the null value to nulldate but it is still saving 01-01-1900 in the field. How can this be done ? 

Regards
khushwant -

1-1-1900 IS "null date". You don't need to have a "NULL" in the column for the system to recognize it as a null date, just use NullDate.

It sounds like your REAL concern is that you want to have another, non-OutSystems application reading that column, and it would be better to have a true NULL in there than 1-1-1900. That's almost always the wrong approach to these things, make a Web Service in OutSystems as a data layer instead.

If the problem is the use of that date, the system allows you (in the eSpace properties) to define which date to use for null date.

If you ABSOLUTELY MUST use a NULL value, use an Advanced Query for the INSERT... but you will be likely causing yourself a long term maintenance headache.

J.Ja
Thanks Justin, Indeed the issue is a non-outsystems app which is expecting a null value if nothing is entered. You said "That's almost always the wrong approach to these things", why is that ? Also regarding the advanced query , I don't want to use it for the same reason you have mentioned i.e maintenance.

Regards
khushwant -

The problem is maintenance, and even worse, compatibility between environments.

For example, in my Dev environment, I may make an entity, use it a bit, remove it, then recreate it with different attributes, all before I deploy to Test or Production. That means that in my Dev environment, the current table name will have a number appended to it (like "OSUSR_81P_ENTITYNAME1") while in Test and Production it won't (it would be "OSUSE_81P_ENTITYNAME"). Another issue is static entities; the static entities often will have different IDs in different environments (the weird pattern I see is that Dev will have different IDs from Test, but Test and Production will have the same IDs), which means that to reliably look up against a static ID, you need to hardcode in its Service Studio ID value into your script, look up against the Static Entities table to get the right ID value for the item you want to reference, and then use that in your other queries. Working with a table after a code rollback is also going to be a potential issue.

The system does a LOT under the hood to make your life easy, but the trade off is that the database is NOT something that you can write SQL code directly against and expect that your SQL will work reliably in each environment or after deployments, unless your code is written in a way like the system works, where it always looks things up at run time to get the right values. It is much, much easier to just expose a SOAP or REST service from your application than to try to make your SQL work right under all conditions.

J.Ja
Justin James wrote:
If the problem is the use of that date, the system allows you (in the eSpace properties) to define which date to use for null date.
 
I just made a post discussing a date issue I'm having that would partially be solved by changing the value for NullDate() but I've been all over the eSpace properties and the documentation and cannot find the setting you reference here.  Can you point me in the right direction?
Curt -

I couldn't find it either, I tried. I could swear on my life I saw it a one point, and now I can't. Maybe I was thinking of some weird place for it, like Integration Studio when connecting to an outside DB? I don't know. But I remember being able to change the value used for NullDate() at one point. :(

J.Ja
Curt, Did you check default value behavior in integration studio ? Here is what it says:

Convert to/from Null value in Database: all the entity's attributes that have the default value set are stored in the database with the null value. When retrieving data from the database, the null value is converted to the attribute's default value. If no default value is defined for the attribute,  the null value is then converted to the Platform default value for the data type.

Here is the link http://www.outsystems.com/help/integrationstudio/8.0/default.htm#Managing_Extensions/Extension_Properties.htm

Regards
ksah -

Yes, that confirms where I remember seeing it.

J.Ja
J.Ja, Glad I could help. Don't know if this is what curt is looking for.

Regards