7. Understanding ODBC Connectivity : ODBC Programming : Date/Time Columns and Values
Share this page                  
Date/Time Columns and Values
Prior to Ingres 9.1, the ODBC Driver supported SQL_C_TYPE_DATE, SQL_C_TYPE_TIME, and SQL_C_TYPE_TIMESTAMP for the Ingres "date" data type. In Ingres 9.1 and later, support was added for ISO date/time data types, including:
Time with local time zone
Time with time zone
Time without time zone
Timestamp with local time zone
Timestamp with time zone
Timestamp without time zone
Ansidate (also known as "ISO" date)
Ingresdate (formerly known as "date")
Year to month interval
Day to second interval
The ODBC driver supports all ISO data types in addition to the legacy "ingresdate" type. The ODBC driver is sensitive to the connection level of the target database, and thus can work seamlessly against pre-Ingres 9.1 installations.
The "precision" argument for SQLBindParameter() is supported for SQL_C_TYPE_TIMESTAMP in Ingres 9.1 and later, since ISO timestamps can be declared with a precision for fractions of a second.
If a date/time column is bound to a string type such as SQL_C_CHAR or SQL_C_WCHAR, Ingres rules regarding II_DATE_FORMAT apply, just as if the dates were handled from the Terminal Monitor or other Ingres utility.
Ingres rules on II_DATE_FORMAT do not apply if:
An ODBC date/time escape sequence is used such as:
{ t 'hh:mm:ss ' }
{ d 'yyyy:mm:dd' }
{ ts 'yyyy:mm:dd hh:mm:ss.fffffffff' }
{ interval 'yy-mm' year to month' }
{interval 'dd hh-mm-ss' day to second' }
ODBC date/time structures are used, such as:
ODBC Support for ANSI Syntax
The ODBC Driver supports ISO 8601 syntax in drivers released with Ingres 9.1 and later. ISO syntax is commonly referred to as ANSI syntax.
ANSI syntax is enforced as follows:
The ODBC timestamp escape sequence { ts 'YYYY-MM-DD HH:MM:SS.[FFFFFFFFF]' } is converted to the ANSI string "TIMESTAMP 'YYYY-MM-DD HH:MM:SS.[FFFFFFFFF]'".
The ODBC date escape sequence { d 'YYYY-MM-DD' } is converted to the ANSI string "DATE 'YYYY-MM-DD'".
The ODBC time escape sequence { t 'HH:MM:SS' } is converted to the ANSI string "TIME 'HH:MM:SS'".
The ODBC escape sequence { interval 'YY-MM' } is converted to the ANSI string "INTERVAL 'YY-MM' year to month", where YY represents the number of years.
The ODBC escape sequence { interval 'DD HH:MM:SS.[FFFFFFFF]' } is converted to the ANSI string "INTERVAL 'DD HH:MM:SS.[FFFFFFFFF]' day to second", where DD represents the number of days.
Support for Ingres Date Syntax
The Ingres syntax for date/time data types supports many of the format rules in ISO 8601, but extends or departs from the standard in several ways. The INGRESDATE data type is overloaded to represent dates, timestamps, times, and intervals in one type.
The INGRESDATE syntax is enforced as follows:
The ODBC timestamp escape sequence { ts 'YYYY-MM-DD HH:MM:SS' } is converted to the INGRESDATE string TIMESTAMP 'YYYY_MM_DD HH:MM:SS'. Fractions of a second are ignored.
The ODBC date escape sequence { d 'YYYY-MM-DD' } is converted to the INGRESDATE string 'YYYY_MM_DD 00:00:00'.
The ODBC time escape sequence { t 'HH:MM:SS' } is converted to the INGRESDATE string 'YYYY_MM_DD HH:MM:SS', where YYYY_MM_DD is filled with the current date when inserted into a database, and represents the insertion date when fetched from the database.
The ODBC Driver supports no Ingres equivalent of interval escape sequences.
The special Ingres syntax for dates is: YYYY_MM_DD.
Special Date Values Meaning "TBD"
Null Dates
Databases need to recognize a date value that corresponds to "TBD" or "Unknown". Null dates are suitable for this purpose. Ingres supports null dates for both ANSI date/time types and INGRESDATE types. The treatment in ODBC is the same as for other null data components.
Empty Dates
Ingres supports a specific type of date value that is called an "empty" date. An empty date is similar to an empty string: the contents of an empty date are non-null, but have no data. In practice, empty dates can perform the same function as null dates. Both indicate the absence of a valid date.
Magic Dates
A magic date is a specific date that represents a different value than the date itself. The ODBC uses the magic date 9999-12-31 to represent empty dates, and 9999-12-31 23:59:59 to represent empty timestamps. If Ingres syntax is in effect, any date or timestamp parameter containing the date 9999-12-31 is converted to an empty date. Similarly, if fetching an empty date into SQL_C_TIMESTAMP or SQL_C_DATE, the result is converted to the magic date.
Default Treatment of Date/Time Syntax
If the ODBC driver is connected to a pre-Ingres 9.1 database, including gateways and EDBC, Ingres syntax is applied for date and time values.
If the ODBC driver is connected to an Ingres 9.1 or later database, ANSI syntax is applied for date and time values.
If both connection attributes DateAlias and SendDateTimeAsIngresDate are left unspecified, the default behavior of the driver may change. The following table shows how DateAlias and SendDateTimeAsIngresDate interact for undefined attributes:
Resulting Values
ansidate, false
ingresdate, true
ingresdate, true
ansidate, false
ingresdate, true
"False" Magic Dates
ODBC does not know whether a given column is an ANSI date/time type or an Ingresdate type. This ambiguity can cause problems.
For example, 9999-12-31 is a valid ANSI date. Therefore, if the ODBC driver and the Ingres database support ANSI syntax, applications that insert dates of 9999-12-31 into ingresdate fields will retrieve 9999-12-31, but the result is not an empty date. Instead, this is the actual date 9999-12-31. This is a "false" empty date.
Note:  If your application uses INGRESDATEs, the default ANSI syntax can cause corrupt data, or cause search queries to fail. Specify the Ingres ODBC configuration attribute "Send Date/Time as Ingres Date" or connection string attribute "SendDateTimeAsIngresDate=y" to force the ODBC Driver to use INGRESDATE syntax.