Oracle database 11.2.
https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG264 says:
Converting Time Zones With the AT TIME ZONE Clause
A datetime SQL expression can be one of the following:
- A datetime column
- A compound expression that yields a datetime value [.......]
https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG238 says:
Datetime Data Types
This section includes the following topics:
My SQL*Plus session says:
SQL> select systimestamp at time zone 'Australia/Sydney' from dual;
SYSTIMESTAMPATTIMEZONE'AUSTRALIA/SYDNEY'
---------------------------------------------------------------------------
13-AUG-16 07.42.20.789000 AM AUSTRALIA/SYDNEY
1 row selected.
Which is perfectly fine. But it also says:
SQL> select sysdate at time zone 'Australia/Sydney' from dual;
select sysdate at time zone 'Australia/Sydney' from dual
*
ERROR at line 1:
ORA-30084: invalid data type for datetime primary with time zone modifier
Huh? Isn't sysdate of date data type?
Worse, the error message for the same command in SQL Developer says:
Error starting at line : 24 in command -
select sysdate at time zone 'Australia/Sydney' from dual
Error at Command Line : 24 Column : 8
Error report -
SQL Error: ORA-30084: invalid data type for datetime primary with time zone modifier
30084. 00000 - "invalid data type for datetime primary with time zone modifier"
*Cause: When a time zone modifier is specified, the data type of datetime
primary must be one of the following: TIME, TIME WITH TIME ZONE,
TIMESTAMP, TIMESTAMP WITH TIME ZONE.
*Action:
I didn't know "TIME" was an Oracle database data type.
Am I seeing things? Or is there a bug in the AT TIME ZONE implementation, and one more in the error handling message?
Cheers, mathguy-ro