SQL Server Type Changes

All types shown are supported by CK6. The Connectivity Kit (CK) is able to store and retrieve from/to all the mentioned data types.

SQL Server 2008/2012 to DF mappings

The following tables show how the SQL Server 2008/2012 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 to nanoseconds). - The length on the DataFlex side will vary depending on the number of decimals of the backend 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.

More on date/datetime handling in SQL Server is described further below.

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 DF to SQL mappings

The following table shows the DF to SQL mappings in CK5 and the allowed new mappings in CK6 for SQL Server 2008/2012.

DataFlex Type (CK5)	SQL Type (CK5)	DataFlex Type (CK6)	SQL Type (CK6)	ODBC Type(s)
DF_ASCII	Char	DF_ASCII	Char, varchar, nchar, nvarchar	SQL_CHAR (1), SQL_VARCHAR (12), SQL_WCHAR (-8), SQL_WVARCHAR (-9)
DF_BCD	SmallInt / Int / Numeric	DF_BCD	SmallInt / Int / Numeric
DF_DATE	Datetime	DF_DATE	Date, Datetime	SQL_TYPE_DATE (91), SQL_TYPE_TIMESTAMP (93)
DF_DATETIME	Datetime	DF_DATETIME	Datetime2, Datetime	SQL_TYPE_TIMESTAMP2 (-200), SQL_TYPE_TIMESTAMP (93)
DF_TEXT	Text	DF_TEXT	varchar(max), text, nvarchar(max), ntext	SQL_VARCHAR (12), SQL_LONGVARCHAR (-1), SQL_WVARCHAR (-9), SQL_WLONGVARCHAR (-10)
DF_BINARY	Binary / Image	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 CK will automatically change the mapping to a type that is supported by the connected SQL Server version.

Example: If DF_Date is configured to map to SQL date but you connect 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 changes

SQL Server 2008 introduced new types for date, datetime and time storage. The SQL Server Connectivity Kit version 6 (CK6) supports these new types. Several changes have been made in CK6 to better handle date and datetime types in general. Some changes may cause backward compatibility issues on existing databases when upgrading from CK5 to CK6.

SQL Server date and time types

SQL Server 2008/2012 includes the following date and time types: - time(0) – time(7) - datetime - datetime2(0) – datetime2(7) - smalldatetime - datetimeoffset(0) – datetimeoffset(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 and is specified as hh:mm:ss. Time(7) can have 7 fractional digits (nanoseconds) and is specified as hh:mm:ss.fffffff.

In CK6 time columns will be 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 specified by the Windows regional settings must be used.

Example (Dutch regional settings where decimal separator is a comma):

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

Example (English (United States) regional settings where time values may be returned with AM/PM):

Showln Table.Time7Column

Will be shown as:

"10:22:13.1234567 PM"

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 2008, datetime was the only type available for both date and datetime columns. The Connectivity Kit would map both DF_Date and DF_Datetime columns to SQL datetime. In CK6 the datetime type can still be used and will function as before.

Datetime values are formatted following Windows regional settings (date separator, date format, decimal separator, time format).

Datetime2

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

In CK6 datetime2 columns will be handled as follows: - 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 datetime2 columns from a DataFlex program, the format specified by the Windows regional settings must be used.

Example (Dutch regional settings: European date format, 24-hour time, comma decimal separator):

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

Example (English (United States) regional settings: US date format, 12-hour time with AM/PM, point decimal separator):

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, whereas datetime2 can store 7 decimals. Storing or retrieving datetime2 values with more than 3 decimals will be truncated to 3 decimals by the DataFlex runtime.

Date

The SQL Server date type stores date values (yyyy-mm-dd). The SQL Server date type will be mapped to DF_Date. When storing or retrieving date columns from a DataFlex program, the date format specified by the Windows regional settings must be used.

Datetimeoffset

Datetimeoffset is similar to datetime2 but can also 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: two digits ranging from 00 to 14 (hours in the offset). - mm: two digits ranging from 00 to 59 (additional minutes in the offset). - + or -: mandatory sign indicating whether the offset is added or subtracted from UTC. - Valid range: -14:00 to +14:00.

In CK6 the datetimeoffset type will be mapped to DF_Datetime.

Smalldatetime

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

In CK6 smalldatetime is mapped to DF_Datetime. The Connectivity Kit will return seconds as 00; seconds will not be stored on save.

Date and datetime mappings

SQL Server versions before 2008 did not have a separate date type; date values were stored in a datetime column with an empty time portion. CK5 mapped DF_Date columns to SQL datetime columns. With CK6, 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 CK5 to CK6. The mappings are 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.

If a type is not supported by the connected SQL Server version, the CK will automatically change the mapping to a supported type (fallback behavior).

Example fallback: DF_Date configured to map to SQL date but connecting to SQL Server 2005 will fall back to SQL datetime.

Dummy zero date

The CK 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 NULL. In that case the lowest possible value for the type will be stored in the database as the dummy zero date.

In earlier CK versions the dummy zero date was used only for DF_Date columns. In CK6 the dummy zero date is also used for DF_Datetime columns.

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 the lowest (dummy zero) values are: - Date = 0001-01-01 - Datetime2 = 0001-01-01 - Datetime = 1753-01-01 - Smalldatetime = 1900-01-01

Using Move 0 or Move "" to a Date/Datetime type will set the default DummyZeroDate.

Converting SQL Server date and datetime values

Converting the native type of DF_Date columns from datetime to date and the native type of DF_Datetime columns from datetime to datetime2 will change the default value for these columns. Because the lowest possible value for datetime (1753-01-01) differs from the lowest value for date and datetime2 (0001-01-01), the default dummy zero date values will change 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 will change data in the database and may have consequences for existing applications that check for the dummy zero date 1753-01-01 and perform special handling (DataFlex applications using embedded SQL, external reports, SQL queries, or third-party applications).

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 (CK5)	SQL Type (CK5)	DataFlex Type (CK6)	SQL Type (CK6)
DF_TEXT	Text	DF_TEXT	varchar(max), text, nvarchar(max), ntext
DF_BINARY	Binary / Image	DF_BINARY	varbinary(max), image, binary

The text, binary and image types are no longer recommended. The recommended types are varchar(max) and varbinary(max). These types are supported on SQL Server 2005 and later.