Open topic with navigation
Some implementations of the Open Database Connectivity (ODBC) specification do not support date/time or timestamp columns containing time zone information. As a result, tasks that read from or write to such columns may behave unexpectedly when using ODBC. The time zone information is sometimes ignored, or you may encounter errors during task development or at run time.
This issue can be resolved in the following ways:
When developing a task that accesses database tables via ODBC, columns with a data type such as "time with time zone" or "timestamp with time zone" may or may not appear as expected in the DMExpress Task Editor, and the associated time zone data may not be handled as expected at run time.
At design time, in the Source Database Table dialog of the DMExpress Task Editor, the column may:
At run time, the following may occur:
(DBIOCTIV) data type of column "<column_name>" in database table "<table_name>" is not supported.
The behavior depends on the database management system and ODBC driver being used. To determine how time zone data is handled when using ODBC, refer to your database management system and ODBC driver documentation.
There are several potential workarounds for this issue, some of which may be used in combination.
Use native database access in your DMExpress task instead of ODBC, if available, to avoid ODBC limitations on time zone support.
To check whether DMExpress supports native access to data types containing time zone for your database, see Conversion between <database> data types and DMExpress data types in the DMExpress Help, where <database> is the name of your database management system.
If DMExpress chooses the Text data type by default when extracting a date/time column containing time zone information, you can convert the data to the Date/time data type using either of the following methods:
If you are encountering issues when extracting time zone data from a source table via ODBC, you can cast the data from the affected columns to another data type using a user-defined SQL statement. This requires that your database table source be accessed with a user-defined SQL statement. See the User defined SQL statement section of the Source Database Table dialog topic in the DMExpress Help.
When defining the SQL statement, instead of specifying columns that contain time zone information directly, use a SQL cast operation to cast the column data to another data type, such as a VARCHAR of appropriate length for a text representation of the data. See your database vendor’s documentation for cast syntax and correct length.
If you cast the column to a text data type, you can map it to a text field in DMExpress. You can then define a value using the ToDate function on that text field to use it as a Date/time value within your task. The format string (mask) passed to the ToDate function would need to match the date format of the converted data returned by your database management system. See ToDate function in the DMExpress Help.
For example, the following user-defined SQL statement will retrieve data from a column containing date, time, and time zone offset information from a Microsoft SQL Server database by casting the data to a text representation:
SELECT CAST(time_zone_column AS varchar(34)) FROM schema_name.table_name
If this casted column is mapped to a text field called db_source.time_zone, you can define a DMExpress Date/time value with the following expression, where the date format string corresponds to the format received from SQL Server:
ToDate(db_source.time_zone, 'YEAR-MM0-DD0 HH0:MI0:SF0 ZH0:ZM0')
If you are unable to load time zone data into an ODBC database target due to an error message or data truncation, you can store your time zone data in a different format within your database tables.
For instance, you can store the raw time in one column, and the time zone, converted to another format such as a string or numeric representation, in another separate column. Alternatively, you can combine the date-time and time zone data in a single text-format column, such as a VARCHAR-type column.
You can use DMExpress date manipulation and conversion functions such as ToDate and ToText as needed to convert the data to the new data type of the target database column.
Copyright © 2016 Syncsort All rights reserved.