Skip to content

DF_FILE_USE_DUMMY_ZERO_DATE

Indicates whether dummy zero dates should be used for the table.

Level

Table

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC)

Type

Boolean, permanent

Access

Read Only

Values

  • True
  • False

Syntax

Use cli.pkg
Get_Attribute DF_FILE_USE_DUMMY_ZERO_DATE of {tableNumber} to {BooleanVariable}

Remarks

DataFlex supports zero dates, while SQL databases generally do not. The only way to store a zero date in a column in a SQL database is to store it as NULL. NULL values, when used in indexed columns, considerably slow down finding. Using dummy zero dates allows the driver to interpret a constant date value as if it were the zero date. If that value is in a date column, it will be reported to DataFlex as the zero date. If DataFlex moves a zero date to the column, it will be translated into this value. The constant values are:

Driver Value
SQL Server 0001-01-01
DB2 0001-01-01
ODBC 0001-01-01

This attribute can be set both inside and outside of a Structure_Start ... Structure_End operation. The value of this attribute is stored in the intermediate file using the keyword Use_Dummy_Zero_Date.

The underlying data type may influence the value effectively saved to the column when DF_FILE_USE_DUMMY_ZERO_DATE is True and a date column is left blank.

For MSSQL, for example, the range for date data types is different: - DateTime2: 0001-01-01 through 9999-12-31 - Date: 0001-01-01 through 9999-12-31 - DateTime (obsolete): January 1, 1753, through December 31, 9999

In that case, if a Date/DateTime2 column is left blank and DF_FILE_USE_DUMMY_ZERO_DATE is True, 0001-01-01 will be saved to the column. However, if the column type is DateTime (obsolete), 1753-01-01 will be saved to it.

Default values set in the driver INT file (MSSQLDRV.INT, DB2_DRV.INT, ODBC_DRV.INT) or in the column (backend table structure) will be ignored, and the minimum value accepted by the data type will be saved if DF_FILE_USE_DUMMY_ZERO_DATE is True and Date/DateTime2 columns are left blank. Those defaults will only be applied if DF_FILE_USE_DUMMY_ZERO_DATE is False.

Example

This sample checks whether the Customer table uses dummy zero dates.

Handle hTable
Boolean bUseDummyZero
Move Customer.File_Number to hTable
Get_Attribute DF_FILE_USE_DUMMY_ZERO_DATE of hTable to bUseDummyZero
If (bUseDummyZero) Begin
    Send Info_Box "The Customer table uses dummy zero dates."
End

Example

This sample loops through the OrderHea records and finds all the order headers that have a zero date. It then uses Embedded SQL to get the value actually stored in the back end and shows this.

Procedure ShowDummyDates
    Boolean bFound
    Handle hoSQL hDBC hStmt
    Integer iFetchResult
    String sZeroDate
    Open OrderHea
    Clear OrderHea
    Repeat
        Find Gt OrderHea By 3
        if (Found) move (OrderHea.Order_Date = 0) to Found
        Move (Found) to bFound
        If (bFound) Begin
            Get Create U_cSQLHandleManager to hoSQL
            If (hoSQL <> 0) Begin
                Get SQLFileConnect of hoSQL OrderHea.File_Number to hDBC
                If (hDBC <> 0) Begin
                    Get SQLOpen of hDBC to hStmt
                    If (hStmt <> 0) Begin
                        Send SQLExecDirect of hStmt ("Select Order_Date From OrderHea Where Order_Number =" * String(OrderHea.Order_number))
                        Get SQLFetch of hStmt to iFetchResult
                        If (iFetchResult <> 0) Begin
                            Get SQLColumnValue of hStmt 1 to sZeroDate
                        End
                        Else Begin
                            Move "" to sZeroDate
                        End
                        Showln "Order " OrderHea.Order_Number " has a zero date in SQL it is stored as: " sZeroDate
                        Send SQLClose of hStmt
                    End
                    Send SQLDisconnect of hDBC
                End
                Send Destroy of hoSQL
            End
        End
    Until (Not(bFound))
End_Procedure