Skip to content

SQL Server Type Mappings

Microsoft SQL Server to DataFlex Mappings

The following tables show how the Microsoft SQL Server native types map to a DataFlex type.

Numeric Types

SQL Type DataFlex Type
TinyInt Num 3.0
BigInt Num 14.0
Numeric Num 0.1 – Num 14.8
Decimal Num 0.1 – Num 14.8
Money Num 15,4
SmallMoney Num 6.4
Int Num 9.0
SmallInt Num 4.0
Float Num 14.8
Real Num 14.8
Bit Num 1.0

Date and Time Types

SQL Type DataFlex Type
DateTimeOffset DateTime (34.0)
Time Asc (11) – Asc(19)
Date Date (10.0)
DateTime2 DateTime (23.0) – DateTime(23.7)
DateTime Date (10) or DateTime (23,3)
SmallDateTime DateTime (23.0)

Notes

  • The Time, DateTime2, and DateTimeOffset types can have a 0 to 7 positions decimal part (milliseconds, nanoseconds).
  • The length on the DataFlex side will vary depending on the number of decimals of the back-end type.

Examples

  • Time(0) will map to asc(11)
  • Time(7) will map to asc(19)
  • DateTime2(0) will map to DateTime(23.0)
  • DateTime2(7) will map to DateTime(23.7)

The SQL datetime type will map to either DF_Date or DF_DateTime, depending on the FIELD_TYPE setting in the table.int file.

Character Data Types

SQL Type DataFlex Type
Char(10) Asc(10)
Char(255) Asc(255)
Char(256) Asc(256)
Char(1000) Asc(1000)
Varchar(10) Asc(10)
Varchar(255) Asc(255)
Varchar(256) Text(256)
Varchar(1000) Text(1000)
Varchar(Max) Text(16383)
Text Text(16383)

Unicode Character Data Types

SQL Type DataFlex Type
nchar(10) Asc(10)
nchar(255) Asc(255)
nchar(256) ASC(256)
nchar(1000) Asc(1000)
nvarchar(10) Asc(10)
nvarchar(255) Asc(255)
nvarchar(256) Text(256)
nvarchar(1000) Text(1000)
nvarchar(Max) Text(16383)
nText Text(16383)

Binary Data Types

SQL Type DataFlex Type
binary(50) binary(50)
binary(1000) binary(1000)
varbinary(50) binary(50)
varbinary(1000) binary(1000)
varbinary(max) binary(16383)
Image Binary(16383)
Timestamp Binary(8)

Other Data Types

SQL Type DataFlex Type
XML Text(16383)
UniqueIdentifier Asc(36)
SQL_Variant Not supported

SQL Server DataFlex to SQL Mappings

The following table shows the DataFlex to SQL mappings for Microsoft SQL Server.

DataFlex Type SQL Type ODBC Type
DF_ASCII Char Varchar
NChar NVarchar SQL_CHAR (1)
SQL_VARCHAR (12)
SQL_WCHAR (-8)
SQL_WVARCHAR (-9)
DF_BCD SmallInt/Int/Numeric
DF_DATE Date DateTime
SQL_TYPE_DATE (91)
SQL_TYPE_TIMESTAMP (93)
DF_DATETIME DateTime2 DateTime
SQL_TYPE_TIMESTAMP2 (-200)
SQL_TYPE_TIMESTAMP (93)
DF_TEXT varchar(max) text
nvarchar(max) ntext
SQL_VARCHAR (12)
SQL_LONGVARCHAR (-1)
SQL_WVARCHAR (-9)
SQL_WLONGVARCHAR(-10)
DF_BINARY varbinary(max) image
binary SQL_VARBINARY (-3)
SQL_LONGVARBINARY (-4)
SQL_BINARY (-2)

Note: When a type is not supported by the SQL Server version, the driver will automatically change the mapping to a type that is supported by the connected-to SQL Server version.

For example, when DF_Date is configured to be mapped to SQL date, when connecting to SQL Server 2005 (which does not have a SQL date type), the mapping will fall back to mapping DF_Date to SQL datetime.

SQL Server Date and Datetime Types

SQL Server 2008/2012 has the following date and time types:

  • Time(0) – Time(7)
  • DateTime
  • DateTime2(0) – (7)
  • SmallDateTime
  • DateTimeOffset(0) – (7)
  • Date

Microsoft recommends using the time, date, DateTime2, and DateTimeOffset data types for new work. DateTime and SmallDateTime are no longer recommended.

Time

Time columns can have a fractional part with a length of 0 to 7. Time(0) has no fractional part, it is specified as hh:mm:ss. Time(7) can have 7 fractional digits (nanoseconds) specified as hh:mm:ss,fffffff.

In driver version 6, time columns are handled as follows:

  • Time columns are shown as DF_ASCII columns.
  • When an invalid time value is entered that will not be accepted by the backend, an Invalid_Time error will be raised.
  • When storing or retrieving time columns from a DataFlex program, the format as specified by the Windows regional settings must be used.

Example:

With regional settings set to Dutch (Netherlands), the decimal separator must be a comma.

Move "22:12:13,1234567" To Table.Time7Column

With regional settings set to English (United States), time values will be returned with AM/PM.

Showln Table.Time7Column

Will be shown as:

"10:22:13.1234567 PM"

Datetime2

The DateTime2 type has the general format yyyy-mm-dd hh:mm:ss.fffffff. The fractional part can be 0 to 7. DateTime(0) has no decimals, DateTime(7) has 7 decimals.

In driver version 6, DateTime2 columns will be handled as follows:

The lowest possible value for a SQL Server datetime column is 0001-01-01.

  • DateTime2 columns are shown as DF_DATETIME columns.
  • When an invalid datetime value is entered that will not be accepted by the backend, an Invalid_DateTime error will be raised.
  • When storing or retrieving datetime columns from a DataFlex program, the format as specified by the Windows regional settings must be used.

Datetime

The SQL Server datetime type consists of a date and a time and a fractional part of max 3 (milliseconds). The lowest possible value for a SQL Server datetime column is 1753-01-01.

In SQL Server versions before SQL Server 2008, datetime was the only type available for both date and datetime columns. The driver would map both DF_Date and DF_DateTime type columns to SQL datetime.

In version 6, the datetime type can still be used and will function as before with earlier versions of the driver.

Datetime values are formatted following Windows regional settings (date separator, date format, decimal separator, time format (24-hour or 12-hour)).

Example:

With regional settings set to Dutch (Netherlands), the date format must be European, the time format must be 24-hour, and the decimal separator must be a comma.

Move "31-12-2012 22:12:13,1234567" To Table.DateTime7Column

With regional settings set to English (United States), the date format must be US, the time format 12-hour (AM/PM notation), and the decimal separator must be a point.

Showln Table.DateTime7Column

Will be shown as:

"12/31/2012 10:22:13.1234567 PM"

Note: The DataFlex runtime datetime type can handle only 3 decimals, where the DateTime2 type can store 7 decimals. As a consequence, storing or retrieving DateTime2 values with more than 3 decimals will be truncated.

Date

The SQL Server date type can be used to store date values (dd/mm/yyyy).

The SQL Server date type will be mapped to DF_Date.

When storing or retrieving date columns from a DataFlex program, the date format as specified by the Windows regional settings must be used.

DatetimeOffset

This is another datetime type that is basically the same as the DateTime2 type, but in addition, it can specify a timezone offset.

The format is:

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]

A time zone offset specifies the zone offset from UTC for a time or datetime value. The time zone offset can be represented as [+|-] hh:mm:

  • hh is two digits that range from 00 to 14 and represent the number of hours in the time zone offset.
  • mm is two digits, ranging from 00 to 59, that represent the number of additional minutes in the time zone offset.
  • + (plus) or - (minus) is the mandatory sign for a time zone offset. This indicates whether the time zone offset is added or subtracted from the UTC time to obtain the local time. The valid range of time zone offset is from -14:00 to +14:00.

In driver version 6, the DateTimeOffset type will be mapped to DF_DateTime.

SmallDateTime

This type stores datetime values in the general format: yyyy-mm-dd hh:mm. Note this type does not store seconds.

In driver version 6, SmallDateTime is mapped to the DF_DATETIME types.

The driver will return seconds as 00. Seconds will not be stored on save.

Date and Datetime Mappings

DF_Date columns can be configured to map to either date or datetime.

This mapping change may have consequences for existing databases/applications when upgrading from driver 5 to driver 6.

The following table shows how DF_Date and DF_DateTime types are mapped to SQL Server types.

DataFlex Type SQL Type
DF_DATE Date
DateTime
DF_DATETIME DateTime2
DateTime

The mappings will be applied only for new columns. Restructuring existing columns will keep their existing types.

The mappings can be configured with the MAP_DFDATE_TO_SQLTYPE and MAP_DF_DATETIME_TO_SQLTYPE settings or attributes.

When a type is not supported by the SQL Server version, the driver will automatically change the mapping to a type that is supported by the connected-to SQL Server version.

For example, when DF_Date is configured to be mapped to SQL date, when connecting to SQL Server 2005 (which does not have a SQL date type), the mapping will fall back to mapping DF_Date to SQL datetime.

Dummy Zero Date

The driver uses the concept of dummy zero dates to handle empty/null/zero date and datetime values.

A dummy zero date will be used if an empty/null/zero date/datetime is stored in the database and the backend column is defined as not-nullable.

In that case, the lowest possible value for the type will be stored in the database as the dummy zero date.

  • All DF_Date and DF_DateTime types will use the DummyZeroDate.
  • DummyZeroDate will be the lowest possible value for the SQL type.

For SQL Server:

  • Date = 0001-01-01
  • DateTime2 = 0001-01-01
  • DateTime = 1753-01-01

  • SmallDateTime = 1900-01-01

  • Move 0 or '' to Date/DateTime type will set the Default_Dummy_Zero_Date.

Converting SQL Server Date and Datetime Values

The example above converts the native type of DF_Date columns from datetime to date and the native type of DF_DateTime columns from datetime to DateTime2.

Since the lowest possible value for the datetime type (1753-01-01) is different from the lowest value for date and DateTime2 (0001-01-01), the default value for the columns will also be changed from 1753-01-01 to 0001-01-01.

Important!

The conversion from datetime to date or DateTime2 will also convert all dummy zero date values from 1753-01-01 to 0001-01-01.

This is important to realize. The conversion will actually change the data in the database. This may have consequences for existing applications. There are applications that check for the dummy zero date value 1753-01-01 and do something special with it. This can be DataFlex applications (embedded SQL) or external applications (DataFlex Reports, SQL or any third-party application).

SQL Server Text and Binary Changes

The following table shows how DF_Text and DF_Binary types are mapped to SQL Server types.

DataFlex Type SQL Type
DF_TEXT varchar(max)
text
nvarchar(max)
ntext
DF_BINARY varbinary(max)
image
binary

The text, binary, and image types are no longer recommended.

The recommended types to use are varchar(max) and varbinary(max). These types are supported on SQL Server 2005 and later.