Using SQL Server 2008 date/time types

SQL Server 2008 introduces new SQL type to store date/time information.

Starting with Genero 2.10, SQL Server 2008 is now supported with the dbmsncA0 driver.

SQL Server 2008 introduces new data types to store date/time information, namely TIME(n), DATE, DATETIME2(n) and DATETIMEOFFSET(n). These data types offer better precision for data storage, as well as a one-to-one mapping for equivalent Genero BDL types (BDL DATE = SQL Server DATE).

When connected to SQL Server 2008 and higher, Genero database drivers for SQL Server use these new data type, when converting a CREATE TABLE / ALTER TABLE statement, and to store Genero BDL DATE and DATETIME information.

Therefore, upgrading to SQL Server 2008 requires planning the SQL Server database migration, in order to use the new date/time types provided in SQL Server 2008.

Note:

Up to Genero version 2.50, ODI drivers are built for a given database client and database server version. For example, dbmftm90 (using FreeTDS) is designed for SQL Server 2005, and will use old date/time types. Pay attention to the fact that drivers designed for an old SQL Server version (2005), can also work with a more recent SQL Server version (2008), but will act as if they were connected to SQL Server 2005 (using old date/time types).

Starting with Genero 3.00 (2.51 for Genero Mobile), ODI drivers can detect the database server version at runtime, and adapt the SQL conversions to the target server: Assuming that the FreeTDS version supports the required TDS protocol version to connect to the target server version, the dbmftm_0 ODI driver can connect to SQL Server 2005 or 2008, and adapt the date/time type usage to the targeted SQL Server version.

However, since Genero 3.10, SQL Server 2005 is no longer supported and only new SQL Server 2008 date/time types can be used.

For more details about BDL to SQL Server type mappings, see DATE and DATETIME data types and Data type conversion table: Informix to SQL Server.