Date Data Type
Date data type considerations include several common to all Enterprise Access gateways as well as some that are gateway-specific. This section presents the general considerations followed by those associated with individual gateways.
General considerations include the following:
• Sending Ingres dates to the gateway
• Supported native date data type formats
• Date constants
• Date function
• Null indicator
• Date interval
• Date range
• Date Without Time Component
• Date empty strings
• Empty Date Processing
• Timezone Adjustment Behavior
• Year 2000 support
How Dates Can Be Sent to the Gateway
An Ingres client can send dates to the gateway in three ways:
• Native Ingres date data type
• Ingres date string literal
• Host DBMS datetime string literal
Valid Date Formats
Valid date formats follow the ISO date format standard (without sub-seconds), as shown in the following table:
Supported Date Data Type Formats
The Enterprise Access gateway supports the native Ingres date data type formats sent by an Ingres client. See the Absolute Date Input Formats section in the Ingres OpenSQL Reference Guide for a list of these formats.
These formats are set by the client's II_DATE_FORMAT environment variable. During connection processing, the client sends this information to the gateway. The supported date formats apply only to Ingres date string literal values sent by the client to the gateway for processing.
Date Constants
The Enterprise Access gateway supports the “today” and “now” date constants. All date constants must be entered with the date function. However, when using date constants with database procedures, there is no need to typecast the string to date. For example:
insert into table_name values ( date ( 'today' ));
insert into table_name values ( date ( 'now' ));
insert into table_name values ( date ( :t1 ))
/*where t1 is host variable and
contains 'today' literal string*/
insert into table_name values ( date ( :t2 ));
/*where t2 is host variable and
contains 'now' literal string*/
execute procedure gw_dateproc (mydate = :t1);
/*where t1 is host variable and contains 'today'*/
execute procedure gw_dateproc (mydate = 'now')
Date Function
All date data values and literals need to be entered with the date function. However, when using date string literals or constants with database procedures, there is no need to typecast the string to date. There is also no need to typecast the date host variables in OpenROAD because the date conversion is handled automatically. For BYREF parameters in a database procedure, a date typecast must not be used.
For example:
insert into table_name values ( date ( :t3 ))
/*where t3 is host variable and
contains valid date input format data*/
insert into table_name values ( date ( '12/05/2005' ));
insert into table_name values ( :d1 );
/*where d1 is date host variable and
contains 'date('now')'*/
insert into table_name values ( :d2 );
/*where d2 is date host variable in OpenROAD and
contains valid date input format data*/
execute procedure gw_dateproc (mydate=:t4);
/*where t4 is host variable and contains 'now'*/
execute procedure gw_dateproc (mydate=byref(:t5));
/*where t5 is host variable*/
The following format is not guaranteed:
insert into table_name values ( :t6 )
/*where t6 is host variable and
contains valid host date input format data*/
If t6 contains '12/05/98', it could be interpreted as May 12th or December 5th depending on the II_DATE_FORMAT setting.
The following format is invalid:
execute procedure gw_dateproc (mydate = byref(date(:t5)));
Null Indicator
In embedded SQL, the Enterprise Access gateway supports the processing of the null indicator for date. For example:
insert into table_name values ( date ( :t1:ind ))
/*where t1 is host variable and
contains 'today' literal string,
and :ind is null indicator for
host variable.*/
Date Interval
The date interval is not supported. For example, the following statement is rejected by the gateway:
insert into table_name values ( date ( :t3 ) )
/*where t3 is host variable and
contains ' 2 days' literal strings.*/
insert into table_name values ( date ( '2 days' ));
Date Range
The Ingres date data types have a valid range between:
1 JAN 0001
and
31 DEC 9999
If the Enterprise Access DBMS contains a date that is beyond this range, the gateway cannot perform date conversion and will fail the query. The application developer needs to consider the host DBMS behavior with regard to date ranges.
Date without Time Component
Unlike Ingres, Enterprise Access does not store dates without time values. All dates handled in Enterprise Access must have a time component. If a date is sent to Enterprise Access without a time component, a time component of 00:00:00 is added before the value is stored into the underlying DBMS. This behavior also applies to the date constant “today”.
Date Empty Strings
Ingres allows an empty string ('') to be specified for a date data type. If the gateway receives an empty string as an Ingres date string literal value for an insert or update query, it converts this into the following default value before storing it in the Enterprise Access database:
01-jan-1900 00:00:00
The value to be used in place of the empty string is configurable. See the chapter “Gateway Configuration Variables” in the Administrator Guide for more information about the empty_date configuration parameter. If there is no entry for empty_date in config.dat, Enterprise Access uses the default value.
If another empty date value is to be returned to the application, configure the empty_date_return configuration parameter. See the chapter “Gateway Configuration Variables” in the Administrator Guide for more information.
It is the responsibility of the application logic to properly interpret this behavior.
How Empty Dates Are Processed
Ingres allows empty dates to be processed. Empty dates are commonly used by Ingres applications. Microsoft SQL, Oracle, and ODBC hosts require that a valid date be present. The application must take into account the behavior of empty date handling.
The gateway handles empty dates as follows:
• The empty_date parameter in config.dat (configurable in CBF/VCBF) contains a default date value that is used if an empty date is received.
• The gateway replaces an empty date with the default (01-jan-1900 00:00:00).
• By default, the date retrieved from the DBMS is the value of empty_date.
• To retrieve an empty date value, set the empty_date_return parameter to EMPTY in CBF/VCBF.
Time Zone Adjustment Behavior
The Enterprise Access gateway handles time zone adjustment differently than Ingres. That is, the DATE fields do not undergo the same time zone adjustment process as Ingres. When dealing with DATE fields in the gateway, it is important to understand how date values are stored and retrieved across different time zones so that proper measures can be taken to ensure date value consistency.
How Time Zone Adjustment Behavior Differs between Ingres and the Gateway
The general difference in time zone adjustment between Ingres and the gateway is that Ingres performs the time zone adjustment, but the gateway does not. Ingres converts the client date to GMT, and then stores the date value as GMT. When the date value is retrieved, the Ingres client applies a time zone adjustment so that the time is always displayed in the client time zone format. The gateway does not perform the time zone adjustments in the same way as Ingres. It inserts the date as is, stores the value as is, and retrieves the value as is. Beware that unlike in Ingres, date values input into the gateway without times will become dates with times inside the gateway.
Reasons for Not Making Time Zone Adjustment in the Gateway
The gateway does not perform time zone adjustment so that results will be consistent with non-Ingres native tools such as SQL Analyzer for Microsoft SQL Server. Non-Ingres native tools do not apply time zone adjustments the same way as Ingres. Enterprise Access stores the date in the format native DBMS tools expect; the gateway inserts the date into the host DBMS as-is—that is, no time zone adjust occurs before saving it to the database. Therefore, client dates are stored in the database as they appear on the client site. To display the same results as the non-Ingres native tools, dates retrieved from the gateway also appear to the client as-is. It is critical that the date values be handled this way to ensure that the results are the same whether an Ingres client is accessing the gateway or a native tool is accessing the host DBMS.
How Date Values Differ Between Ingres and the Gateway
Differences in date values between Ingres and the gateway can only be observed when the date value is entered in one time zone but accessed in another time zone. Assume that II_DATE_FORMAT is set to US and mytable is a table with a single date column, mydate. The following example shows the value of mydate in Ingres and Microsoft SQL when running the queries from two Ingres clients with time zones that are 2 hours apart (5 AM and 7 AM, respectively):
Notice that for Ingres, 7 AM entered in client 1 is displayed as 5 AM in client 2 and, because of the time zone adjustment, 5 AM updated in client 2 is displayed as 7 AM in client 1. For the gateway, regardless of the time zone the date value is entered in, it is stored and displayed as the same value across a different time zone. No time zone adjustment is applied. This behavior is consistent with the native non-Ingres DBMS tools.
Consistent Transaction Times and Behavior in Applications
To ensure consistent transaction times in your applications, do not mix functions that make the time zone adjustment (such as Ingres' date('now') and date('today')) with gateway host DBMS functions that do not make the time zone adjustment (such as Microsoft SQL 's getdate()). To obtain date results that are consistent with the native tools of a non-Ingres DBMS, do not mix date values retrieved from the gateway DBMS with Ingres' date('now') or date('today'). Mixing date functions that use different time zone adjustment rules will produce unexpected results and misleading information.
It is also a good practice to perform all arithmetic, particularly date arithmetic, on the client side to produce consistent behavior. When using date values in OpenROAD applications, it is strongly recommended that the DATE data type (as opposed to string variable or literal) be used.
Year 2000 Support
A logical symbol, II_DATE_CENTURY_BOUNDARY, accommodates the year 2000. (The client passes the value of this symbol to the gateway.) The gateway provides features for users who want to use dates in the 20th century in addition to the current century.
The gateway handles date input and output as follows:
• Year defaults to the current year.
• In formats that include delimiters (such as forward slashes or dashes), you can specify the last two digits of the year; the first two digits default to the current century. For example, if you enter '03/21/93' using the format mm/dd/yyyy, the gateway assumes that you are referring to March 21, 2093.
This behavior forces the user to specify all four digits of the date when dealing with dates in the prior century. Refer to the next section for ways to handle this behavior.
Note: Using four digits to accommodate year information in your applications is strongly recommended.
How II_DATE_CENTURY_BOUNDARY Works
To modify date handling behavior, the gateway provides an environment variable II_DATE_CENTURY_BOUNDARY which may be set with an integer value in the range 0 < n <= 100. On date input, if the century part of the date is not specified, (the date_year component is < 100 and there is a valid setting of II_DATE_CENTURY_BOUNDARY), then the century is determined by the calculation:
if ( date_year < II_DATE_CENTURY_BOUNDARY )
then
(date_year += 2000)
else
(date_year += 1900)
endif
That is, if II_DATE_CENTURY_BOUNDARY is 90, then an input date of 03/21/93 will be treated as March 21, 1993, whereas a date of 03/21/03 becomes March 21, 2003.
This setting should be made in the installation where the gateway server resides.
This setting applies to the handling of string literals. It is recommended that the date data type be used instead of string literals.