DATETIME-TZ function does not convert datetime-tz value to a different time zone, when the timezone argument is not supplied

DATETIME-TZ function does not convert datetime-tz value to a different time zone, when the second timezone argument is not specified.

Jun 18, 2024
Knowledge

Title

URL Name

000061871

Article Number

000177040

Environment

Product: OpenEdge Version: 10.x, 11.3.x - 11.7.x, 12.x OS: All supported platforms

Question/Problem Description

According to the documentation, the timezone of a DATETIME-TZ value can be converted to a different time zone using the following function:

DATETIME-TZ ( datetime-tz-exp [ , timezone-exp ] ).
datetime-tz-exp => an expression whose value is a DATETIME-TZ. Use this option to convert a DATETIME-TZ from one time zone to another.
timezone-exp => optional parameter, an expression whose value is an integer representing the time zone offset from Coordinated Universal Time (UTC) in minutes. If not specified, the function uses the session’s time zone.

When timezone-exp is not specified DATETIME-TZ function does not convert datetime-tz-exp value to a different time zone.

Steps to Reproduce

Clarifying Information

1. Run the following code.

DEFINE VARIABLE dtLocalTime AS DATETIME NO-UNDO.
DEFINE VARIABLE dtStoredTime AS DATETIME-TZ NO-UNDO.

dtStoredTime = DATETIME-TZ(1,1,2015,12,0,0,0,0).

dtLocalTime = dtStoredTime.

MESSAGE "Stored Time: "                           dtStoredTime              SKIP
"Local Time DATETIME-TZ(DATETIME):    "           DATETIME-TZ(dtLocalTime)  SKIP
"Local Time DATETIME-TZ(DATETIME-TZ): "           DATETIME-TZ(dtStoredTime) SKIP
"Local Time DATETIME-TZ(DATETIME-TZ, timezone): " DATETIME-TZ(dtStoredTime, 60)
VIEW-AS ALERT-BOX.

2. Notice the different results when timezone-exp is and is not specified in the DATETIME-TZ function. When timezone-exp is not specified DATETIME-TZ function does not convert datetime-tz-exp value to a different time zone, but keeps the initial value.

NOTE: The client session parameter -useSessionTZ introduced in OpenEdge 11.6.1 and defaultly set to 1 from OpenEdge 11.7 onwards, does not change described behavior.

Error Message

Defect Number

Defect PSC00336136 / OCTA-19102

Enhancement Number

Cause

It was determined that the Progress Product is functioning as designed and will not be changed.

The documentation is confusing about the use of datetime-tz-exp and timezone-exp parameters:

When the optional timezone argument is not supplied to the DATETIME-TZ function, the timezone applied to the datetime-tz return value is the current UTC offset of the session, not the UTC offset for the date/time supplied.
The time zone offset from Coordinated Universal Time (UTC) in minutes needs to be specified in the timezone argument to get the session's time zone: DATETIME-TZ(datetime-tz-exp, TIMEZONE)

Resolution

Define timezone-exp (second parameter) of DATETIME-TZ function:

DATETIME-TZ ( datetime-tz-exp, timezone-exp ).

Workaround

Notes

Progress Articles:

How to convert stored DATETIME-TZ value to local time
Inconsistency assigning DATETIME-TZ value directly to DATE variable vs using the DATE function to extract the date
Assigning datetime-tz to datetime does not use SESSION:TIMEZONE

Keyword Phrase

Last Modified Date

6/18/2024 11:50 AM

DATETIME-TZ function does not convert datetime-tz value to a different time zone, when the timezone argument is not supplied

Files(0)

Was this article helpful?

Was this article helpful?

DATETIME-TZ function does not convert datetime-tz value to a different time zone, when the timezone argument is not supplied

Information

Related Articles