DataFlex Date in the Microsoft SQL Server World

Microsoft SQL Server includes a few date and time data types that can be used in SQL tables. The choice of which data type to use depends on the data developers need to store and retrieve from those columns.

Before the release of SQL Server 2008, datetime and smalldatetime were the only data types available in SQL Server to store date and time data. When converting tables from the native DataFlex database to Microsoft SQL Server, DataFlex date fields were automatically converted to use datetime columns as it would encompass a larger date range.

With the release of SQL Server 2008, four new data types were introduced: time, date, datetime2 and datetimeoffset. Those types are fully supported in MS SQL Connectivity Kit version 6 and they made it possible for the Connectivity Kit to convert DataFlex date fields to SQL Server date columns and DataFlex datetime fields to datetime2 columns.

In order to offer the flexibility of working with various versions of SQL Server and keeping applications backwards compatible, a mapping configuration setting was created.

That setting – DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA – gives developers the ability to control which SQL Server data type schema to use when converting DataFlex native tables to Microsoft SQL Server. This setting has no effect on existing columns; once a column of a certain data type is created, its type will remain the same even during a restructure, no matter what the mapping is set to.

The possible values for this setting are MAP_DF_TO_SQL_TYPE_SQL2000 (pre-DataFlex 19.0 default), MAP_DF_TO_SQL_TYPE_SQL2005, MAP_DF_TO_SQL_TYPE_SQL2008 (default), MAP_DF_TO_SQL_TYPE_SQL2012 and are defined as follows:

So when converting tables from native DataFlex to Microsoft SQL Server using current default settings, DataFlex date columns still get converted to SQL datetime columns in the backend database. To change that behavior, you may choose to adjust DEFAULT_MAP_DF_TO_SQL_TYPE_SCHEMA to the schema of the SQL Server version you will be using as your backend or adjust the individual settings for MAP_DFDATE_TO_SQLTYPE and MAP_DFDATETIME_TO_SQLTYPE to the types compatible with your backend version.

Now, if you would like to change the data type of already existing columns, you may use DF_FIELD_NATIVE_TYPE to programmatically modify those columns’ data type in the backend.

If you are connecting to SQL Server tables that have datetime columns, those columns will be automatically mapped to datetime in DataFlex as well. In order to have those datetime columns interpreted as date in DataFlex (and hide the extra information stored in datetime columns), you may use the Studio Table Editor to change the column type to Date, or use DF_FIELD_TYPE to programmatically change the column to DF_Date, or simply remove FIELD_TYPE DATETIME from the table INT file. Those should all deliver the result you are looking for.

DataFlex date fields may be stored and interpreted as either date or the various forms of datetime in SQL Server. It will be up to you, the developer, to decide the best way to display and handle those columns. DataFlex gives you the flexibility; you make the decision.