Dealing with Dataflex Zero date: 1753-01-01

[Bob Cergol]

Hello,

As you all know, Dataflex defines a zero date value that is translated to MS-SQL server as 1753-01-01.

Everywhere I have a "zero" date I want the date value to display as blank. What's the best way to accomplish this?

Thanks,
Bob

[Carsten Sørensen]

Hi Bob,

I'm afraid that the only way to do it is to make a CASE WHEN column... which is not a good solution as it will require that you do it on all date columns.

I believe we can do a global variable (you will need an updated Dynamic AI with such a fix) that could allow you to define a NULL date = 1753-01-01. I'll put on the short list!

Best regards,
Carsten

[Bob Cergol]

I kind of figured you'd advise using a "case when".
A global for that situation would be a very useful feature.
Speaking of global...why not define and include a generic basic "aliasbundle" table in the default repository that ships with DAI. A user could still modify it or create new ones if they wished. It seems to me it would be a fairly standard function DAI developers would be using.

Regards,
Bob

[Carsten Sørensen]

I agree - have actually thought about it - but the first business case we had on it some years ago seemed pleased with having full control outside the repository.
Will include one in a future release!
Thanks for the input!
Best regards,
Carsten

[Carsten Sørensen]

Hi Bob,

We've implemented a zero-date system parameter option for the server/enterprise editions. Will be available with next build.

You can enter a system wide "null" date that will be replaced by Dynamic AI during execution of reports and forms. The functionality will replace e.g.: 1753-01-01 with Null after having retrieved the data from the database.

zerodateSYS.jpg (42.37 KiB) Viewed 16777 times

The functionality should be general - i.e. you should get the NULL / blank presentation on listings, reports, dashboards and forms.

zerodateinaction.jpg (128.83 KiB) Viewed 16777 times

When in ListEditMode the functionality is disabled - in order for Dynamic AI to determine whether data was actually changed (on a record with 1753-01-01) .... just FYI...

Another thing you should be aware of is the following:

1. Pure dates / date time values = ZERODATE will be replaced
2. YEAR parts of dates / date time values (resulting in this case to 1753) will also be presented as blanks
3. Quarter, month and day functions will not. As we can't determine whether 01 is part of the ZERODATE value. In case you would need that - you would have to create a +Column SQL formula with a CASE WHEN to eliminate the ZERODATE. This will be relevant only when you group on Quarter, month etc. without any previous restriction to eliminate the ZERODATEs.

Best regards,
Carsten