In Oracle Database, the TO_TIMESTAMP_TZ()
function converts its argument to a value of TIMESTAMP WITH TIME ZONE
data type.
Syntax
The syntax goes like this:
TO_TIMESTAMP_TZ(char [ DEFAULT return_value ON CONVERSION ERROR ]
[, fmt [, 'nlsparam' ] ])
The char
argument can be any expression that evaluates to a character string of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
data type.
The optional fmt
argument specifies the format of char
. If you omit fmt
, then char
must be in the default format of the TIMESTAMP WITH TIME ZONE
data type, which is determined by the NLS_TIMESTAMP_TZ_FORMAT
initialisation parameter (see How to Check the Date Format of your Oracle Session).
The optional 'nlsparam'
argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:
'NLS_DATE_LANGUAGE = language'
Example
Here’s a basic example to demonstrate:
SELECT
TO_TIMESTAMP_TZ(
'25-Aug-2030 18:10:35.123456789 +09:30',
'DD-Mon-RRRR HH24:MI:SS.FF TZH:TZM'
)
FROM DUAL;
Result:
25/AUG/30 06:10:35.123456789 PM +09:30
The format of the result is determined by your session’s NLS_TIMESTAMP_TZ_FORMAT
parameter. We can check the value of the NLS_TIMESTAMP_TZ_FORMAT
parameter by querying the V$NLS_PARAMETERS
view:
SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_TIMESTAMP_TZ_FORMAT';
Result:
DD/MON/RR HH12:MI:SSXFF AM TZR
The default value of the NLS_TIMESTAMP_TZ_FORMAT
parameter is derived from the NLS_TERRITORY
parameter. In my case the NLS_TERRITORY
parameter is AUSTRALIA
.
Here’s what happens when I change the NLS_TERRITORY
parameter to another territory, then call TO_TIMESTAMP_TZ()
again:
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
SELECT
TO_TIMESTAMP_TZ(
'25-Aug-2030 18:10:35.123456789 +09:30',
'DD-Mon-RRRR HH24:MI:SS.FF TZH:TZM'
)
FROM DUAL;
Result:
25-AUG-30 06.10.35.123456789 PM +09:30
This time the result is returned in a different format.
You can alternatively change the value of the NLS_TIMESTAMP_TZ_FORMAT
parameter directly. This will change that parameter without affecting other parameters. See How to Change the Date Format in your Oracle Session for more information and examples.
Default Format
In this example I omit the fmt
argument:
SELECT
TO_TIMESTAMP_TZ(
'25-AUG-30 06.10.35.123456789 PM +09:30'
)
FROM DUAL;
Result:
25-AUG-30 06.10.35.123456789 PM +09:30
When doing this, the argument must be in the default format of the TIMESTAMP WITH TIME ZONE
data type, which is determined by the NLS_TIMESTAMP_TZ_FORMAT
parameter.
Here’s an example of what happens when we pass a value that doesn’t conform to this format:
SELECT
TO_TIMESTAMP_TZ(
'25-Aug-2030 18:10:35.123456789 +09:30'
)
FROM DUAL;
Result:
Error report - ORA-01849: hour must be between 1 and 12
In this case, I passed a value that uses 24 hour time, but my NLS_TIMESTAMP_TZ_FORMAT
parameter specifies a 12 hour clock with the AM/PM designator.
To fix this, I would either need to change my input, or change the value of the NLS_TIMESTAMP_TZ_FORMAT
parameter.
Provide a Default Value on Conversion Error
You also have the option of providing a value to return in the event that there’s an error while converting the argument to a TIMESTAMP WITH TIME ZONE
type.
Example:
SET NULL 'null';
SELECT
TO_TIMESTAMP_TZ(
'Oops!'
DEFAULT null ON CONVERSION ERROR
)
FROM DUAL;
Result:
null
In this case I specified that null
should be returned whenever there’s a conversion error. The return value can be an expression or a bind variable, and it must evaluate to a character string of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
data type, or null
. The function then converts it to TIMESTAMP WITH TIME ZONE
. If an error occurs during this conversion, then an error is returned.
Regarding the first line in the above example, this is simply to specify what should be returned to my client whenever a null
value occurs. By default, SQLcl and SQL*Plus return a blank space whenever null
occurs as a result of a SQL SELECT
statement.
However, you can use SET NULL
to specify a different string to be returned. Here I specified that the string null
should be returned.
The 'nlsparam'
Argument
The optional 'nlsparam'
argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:
'NLS_DATE_LANGUAGE = language'
Example
SELECT
TO_TIMESTAMP_TZ(
'25-agosto-2030 18:10:35.123456789 +02:00',
'DD-Month-RRRR HH24:MI:SS.FF TZH:TZM',
'NLS_DATE_LANGUAGE = SPANISH'
)
FROM DUAL;
Result:
25-AUG-30 06.10.35.123456789 PM +02:00
Note that the result is still returned according to the current session’s NLS_TIMESTAMP_TZ_FORMAT
parameter. It’s just that the input was in a different language.
Here’s what happens when I change agosto
to August
while using the same 'nlsparam'
value:
SELECT
TO_TIMESTAMP_TZ(
'25-August-2030 18:10:35.123456789 +02:00',
'DD-Month-RRRR HH24:MI:SS.FF TZH:TZM',
'NLS_DATE_LANGUAGE = SPANISH'
)
FROM DUAL;
Result:
Error report - ORA-01843: not a valid month
This happened because I didn’t provide the date in the language specified by the 'nlsparam'
argument (Spanish).
Changing it to English
solves this problem:
SELECT
TO_TIMESTAMP_TZ(
'25-August-2030 18:10:35.123456789 +02:00',
'DD-Month-RRRR HH24:MI:SS.FF TZH:TZM',
'NLS_DATE_LANGUAGE = English'
)
FROM DUAL;
Result:
25-AUG-30 06.10.35.123456789 PM +02:00
See How to Return a List of Supported Languages in Oracle if it helps.
Null Arguments
Passing null
results in null
:
SET NULL 'null';
SELECT
TO_TIMESTAMP_TZ(null)
FROM DUAL;
Result:
null
Invalid Argument Count
Calling the function without passing any arguments results in an error:
SELECT TO_TIMESTAMP_TZ()
FROM DUAL;
Result:
Error report - SQL Error: ORA-00938: not enough arguments for function 00938. 00000 - "not enough arguments for function"
However, passing too many arguments doesn’t seem to cause any issues, as long as the first three are valid:
SELECT
TO_TIMESTAMP_TZ(
'25-Aug-2030 18:10:35.123456789 +09:30',
'DD-Mon-RRRR HH24:MI:SS.FF TZH:TZM',
'NLS_DATE_LANGUAGE = English',
'Oops!',
'Dang!',
'Homer',
'Symptom'
)
FROM DUAL;
Result:
25-AUG-30 06.10.35.123456789 PM +09:30