Skip to content

Improve date selection and handling

What is new - 26.0

The DataFlex embedded database supports a date value of 0 to represent an unknown or not-yet-determined date.

When migrating existing DataFlex data to an external database such as Microsoft SQL Server, there are two common strategies:

  1. Allow NULL values in the target column.
  2. Replace the zero date with a dummy zero date, typically the lowest date supported by the target database system.

For Microsoft SQL Server, the minimum supported date depends on the column data type:

  • datetime: 1753-01-01
  • date / datetime2: 0001-01-01

For RDS (COM), the zero date is 1899-12-30.

Reporting Implications

DataFlex Reports retrieves date values through ODBC. It cannot distinguish between:

  • A legitimate historical date (e.g., 1753-01-01), and
  • A dummy zero date introduced during conversion.

As a result, the report engine will display the value as stored. Suppressing such values is possible via a suppression formula, but the report designer must know exactly which dummy date value to test against.

Complications with Date Comparisons

Additional issues arise when evaluating dates programmatically:

  • Dates earlier than 1601-01-01 cannot be processed by DateSerial, as this is its minimum supported value.
  • Dates in the first century (0001-01-01 through 0099-12-31) may be incorrectly converted to the 21st century, further complicating comparisons and logical tests.

These constraints make reliable detection of dummy dates non-trivial.

Solution: IsDummyZeroDate

To address this, a new function — IsDummyZeroDate — has been introduced.

This function is implemented per database driver and returns the date value that is treated as the zero (dummy) date for the specific column. By using IsDummyZeroDate, report logic and application code can reliably identify and suppress placeholder dates without hard-coding database-specific minimum values.

This approach ensures portability, correctness, and cleaner report design logic.