Understanding the Date & DateTime Data Types

DataFlex has two data types that store similar data: Date and DateTime. It can be confusing for developers how to use one or the other in their code, especially when using DataFlex MSSQL drivers.

What is different about the two data types and what is the recommended way to handle them in an application?

DataFlex Date Data Type

Date is a simple type that is used to declare variables for storing Date tokens.

A DataFlex date token is represented by a sequence of numbers and separators that represent the day, month and year of the specific date. The ordering of the day, month and year digits and the type of separator that is used is configurable and transparent.

By default, DataFlex represents dates in the following format: mm/dd/yyyy. Where mm represents the month (1-12), dd the day (1-31 or whatever the maximum day is for a given month) and yyyy is the year (0001 - 2500).

DataFlex can be configured to use European dates in the format dd/mm/yyyy or military dates in the format yyyy.mm.dd.

The range of values for Date data type is 01/01/0001 to 01/01/2500.

DataFlex DateTime Data Type

The DateTime type corresponds to an SQL_TIMESTAMP_STRUCT as defined in ODBC (https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/c-data-types).

DateTime types are used for declaring variables for storing date and time values with an accuracy down to milliseconds.

The range of values for DateTime data type is 1/1/-32768 12:00:00.000 AM to 12/31/32767 11:59:59.999 PM.

Note that while the DateTime data type can hold any value within this range, other DateTime calculation, manipulation, and storage routines may have other limits and you should consult the documentation of the specific routines for additional limitations.

Date and DateTime in Microsoft SQL

As discussed in the blog post DataFlex Date in the Microsoft SQL Server World, Microsoft SQL Server includes a few Date and DateTime data types that can be used in SQL tables.

Depending on the SQL Server version being used, certain defaults and options will be applied. Those can be customized to adhere to the needs of the application accessing the data. For details on this, refer back to the DataFlex Date in the Microsoft SQL Server World blog post.

All columns in DataFlex have an SQL_Type and a DataFlex type. The Table Editor in the Studio identify those as “Type” (i.e. SQL_Type) and “DataFlex Type.”

When it comes to Date and DateTime data types the following possibilities apply:

1) SQL_Date DF_Date
2) SQL_Datetime2 DF_Datetime
3a) SQL_Datetime DF_Date
3b) SQL_Datetime DF_Datetime

For SQL_DateTime you may select whether to use DF_Date (3a) or a DF_DateTime (3b). In the Studio, that selection can be made by setting the column’s "DataFlex Type."

It is recommended to use SQL_Date (1) for Date columns and SQL_Datetime2 (2) for DateTime columns.

For historical reasons, the combination 3a) is still quite common in existing databases and applications. In versions of DataFlex earlier than 19.0, SQL_Datetime would be mapped to DF_Date by default.

In a scenario where a SQL_Datetime column must be set to DF_Date, first choose SQL_Type SQL_Datetime and then choose DF_Date for DF_Type.

Date and DateTime in Your Code

Regardless of the underlying SQL types, remember that the two data types (DF_Date and DF_DateTime) are different and will need to be handled in different ways in your program.

While DataFlex dates can be set to and handle a zero value, DateTime cannot. The Date data type is a low-level numeric value containing number of days. The datetime data type is internally a timestamp struct that cannot contain a plain numeric value like 0.

For Example:

Date dMyDate
Move 0 to dMyDate // This works
If (dMyDate = 0) Begin // This works
    Showln "dMyDate is 0"
End

DateTime dtMyDatetime
Move 0 to dtMyDatetime // Generates error: Illegal Datatype Conversion
If (dtMyDatetime = 0) Begin // Generates error: Illegal Datatype Conversion
    Showln "dtMyDatetime is 0"
End

This example uses Date and DateTime variables, but it is the same with Date and DateTime columns in a database:

If (Vendor.DateColumn = 0) Begin // This works
    Showln "Date is 0"
End

If (Vendor.DatetimeColumn = 0) Begin // Generates error: Illegal Datatype Conversion
    Showln "Datetime is 0"
End

For DateTime data type you should use IsNullDateTime to check for NULL:

If (IsNullDateTime(Vendor.DatetimeColumn)) Begin
    Showln "Datetime is null"
End

Knowing that will help you prevent errors in your application and get the results you are looking for.

Sources

Evertjan Dondergoor, Software Engineer, Data Access Europe
Martin Moleman, Software Engineer, Data Access Europe
DataFlex Help