Importing dates

Looking for anyone that might have dealt with these date issues.

I have two date related issues that are sort of related to each other.  The data is contained in a spreadsheet and I'm using the import from Excel function to populate my entity.

The data is historical in nature and contains birth dates from around 1850 into the 2000's.  Obviously, it is possible that someone could have been born on 01/01/1900.  When a record with that value is added it is treated the same as a NullDate().  (See this reference -

The data also contains death dates where 01/01/1900 is also valid and in addition the bulk of the death dates are zero, indicating that they haven't died yet.  In a typical database, I would set them to NULL but I don't believe the platform has any way of doing that. 

So, I think I need to do the following.  Create a new function called NullDate1800() that matches the NullDate() functionality except it uses 01/01/1800 instead of 01/01/1900.  Also, anywhere that a date is shown, I need to test for NullDate1800() and if true, show nothing (I would have to do this last part anyway, regardless of the value used for a null date.)  Any help writing such a function or help cloning it from the platform would be great or tell me how crazy I am and show me the easy way to accomplish this.

Curt -

I am fairly positive that I saw ages ago a way of changing what date is used as NULL, but for the life of me I cannot find that again.

All NullDate() does is return #1900-01-01# so NullDate1800() could just return #1800-01-01#. If you need to test for NullDate1800() in a query, pass in a "NullDate1800" parameter in, and set it equal to #1800-01-01#. Doing it like this (instead of hardcoding #1800-01-1# in) lets you change it as you need to.

Ok, thanks!
Well, I started playing with this and hit a more critical roadblock.  The documentation (found here - ) states that the oldest valid date is #1900-01-01# making my idea of using #1800-01-01# pointless.  While obviously I can come up with any number of work arounds but I really hate having to do that.

Hey Outsystems, any chance of getting a hot fix to change the valid date range to be 1800-01-01 to 2999-12-31 instead of the currently documented range?  I need this pretty bad.  I will post this as an idea also.

Curt -

Looks like this may be at the SQL Server level:

You may need to just build a year/month/day table and put an ID reference to that.

Ok, I'm confused.  Right at the top it states:

Date and time data from January 1, 1753 through December 31, 9999

That would work for me.  If I can't get a fix I'm just going to add a boolean to indicate 1800.  Can't see creating a whole table just for this.

Thanks again for your prompt reply!
That being the case you should be able to store dates < 1900-01-01. The issue is the nulldate and for that you can actually add an extra parameter indicating is date is null or not.

From SQL Server 2008 onwards you have datetime2 wich ranges from 01-01-01 to 31-12-9999. You can probaly create a table and import it in Integration Studio manually maping the datetime2 (from SQL) to DateTime (System.DateTime from .NET) - check it out here:

You would still have the issue of NullDate...

I've resigned myself to the fact that I can't use the NullDate value but, as I stated earlier, the Outsystems platform will not allow a date earlier than #1900-01-01#..  Extremely simple for anyone to verify.  Create a session variable with a Date data type and enter #1800-01-01# in the value field and True Change will show the error Invalid 'Date' value.  This little test confirms that the documentation is correct and the problem has nothing to do with SQL.

Curt -

The issue is the smalldate type. If you look at the link I posted, it says in there under "smalldate" that the range starts at 1/1/1900. These are clearly based on integer types under the hood, which means that as the allowed range gets bigger, either precision goes down, or the integer type used has to get wider.

I can see why it would limit you to the range even for a session variable; those go to the DB, so if it can't be written to the DB, it can't go into a Session Variable.

Ok, I know I'm beating a dead horse but why the heck would they use smalldate?  It's just like the int/bigint issue for ID values.  For a generic platform that should be able to do almost anything, these seem like short sighted decisions.  Or at least have a site option to indicate what type should be used.  

I'll use my workaround since it's just the one date.  It's this simple stuff that drives me crazy!

Curt -

There should be the option, definitely (ditto for bigint on IDs).

I'll say that the *precision* of the smalldate is just as important for some apps as having a wider range of dates is for other apps. There was a time, once, where I got bitten... VERY HARD... by even smalldate's precision not being wide enough, and I had a critical bug in Production that was tough to track down as a result! I think someone just flipped a coin, or tried to guess what the more common use case would be. :(

I had a problem like that to solve a while back. But I also had some cases where I only had Month and Year, so I made 12 "no-day dates" and one "year-only date" for each year.

The best workaround is to create an entire table with all possible dates in my interval (I think it was 1800-2200) and id them into the "no-longer-a-date-type" column.
To make it readable, you can use text as id and write it like 20140313, 201403__, 2014____ ....
Thanks Justin and Nuno.  I appreciate your input.