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