About DATETIME Items
Values for Forms items with data type DATETIME
are automatically converted between the server time zone region for the value
stored in the Forms record manager, and the local time zone region for the value
in the client's widget.
This conversion does not apply to items of data type DATE.
Time zone conversions take into account historical data when transitions between
daylight and standard time have occurred (or are anticipated to occur) in the
relevant time zone regions. This historical data is extracted from the time
zone file (FORMS_TZFILE).
An automatic conversion of an ambiguous DATETIME
item value (because it falls into the 2-hour overlap period that occurs once
a year when switching from daylight back to standard time) assumes that the
ambiguous value represents standard time.
About Automatic Conversion of DATETIME Items
There are special considerations regarding the automatic conversion of DATETIME
item values in a block that's in Enter-Query mode. Item values entered
by the end user in enter-query mode will be time-zone adjusted (to produce the
value used in the query's WHERE clause)
when the value is:
- A full DATETIME value.
- A date-only portion of a DATETIME value.
When no time-zone adjustment is in effect, such a value will generate a BETWEEN
clause specifying the entire day (from 00:00:00 through 23:59:59). When time-zone
adjustment is in effect, the BETWEEN clause still specifies a 24-hour range,
but it's adjusted from the local time to server time (and thus might,
e.g., specify a range of 03:00:00 through 02:59:59 on the following day in
the server's time zone).
- A DATETIME value preceded by a relational
operator.
Item values entered by the end user in Enter-Query mode are not time-zone adjusted
(to produce the value used in the query's WHERE
clause) when the value is:
- A DATETIME value containing wildcard character(s)
('%' or '_').
- A value beginning with '#'.
Such a value, in enter-query mode, is interpreted as a fragment of a WHERE
clause, and the characters that were typed following the '#'
are inserted into the WHERE clause with
no change.
- A value beginning with ':' or
'&'. These values are used
in conjunction with the deprecated query-where dialog, and are treated somewhat
analogously to '#' values.
Item values set programmatically by the COPY Built-in are time-zone adjusted
(to produce the value used in the query's WHERE
clause) only when the value is:
- A DATETIME value preceded by a relational
operator. Note that the date portion of such a value must be in the item's
input date format, not in Built-in date format (as COPY values normally are).
- A full DATETIME value or a date-only portion of a DATETIME
value that's not valid for the Built-in date format but is valid for
the item's input date format. (COPY will first attempt to convert such
a value using the Built-in date format - with no time-zone adjustment, and
if that fails, will then attempt to convert it using the item's input
date format - with time-zone adjustment).
Full DATETIME item values that are set programmatically
by a PL/SQL assignment are not time-zone adjusted
Note: :SYSTEM.LAST_QUERY
always shows DATETIME
values in the server's time zone.
Related topics
Datetime_Server_TZ Property
Datetime_Local_TZ Property
FORMS_DATETIME_LOCAL_TZ
FORMS_DATETIME_SERVER_TZ
ADJUST_TZ Built-in
FORMS_TZFILE