NEW_TIME() Function in Oracle

In Oracle Database, the NEW_TIME() function converts the date from a specified time zone to another specified time zone, and returns the result.

Syntax

The syntax goes like this:

NEW_TIME(date, timezone1, timezone2)

So, the function returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date.

Note that before using this function, you’ll need to set the NLS_DATE_FORMAT parameter to display 24-hour time.

The arguments timezone1 and timezone2 can be any of these text strings:

AST or ADT	Atlantic Standard or Daylight Time
BST or BDT	Bering Standard or Daylight Time
CST or CDT	Central Standard or Daylight Time
EST or EDT	Eastern Standard or Daylight Time
GMT	Greenwich Mean Time
HST or HDT	Alaska-Hawaii Standard Time or Daylight Time
MST or MDT	Mountain Standard or Daylight Time
NST	Newfoundland Standard Time
PST or PDT	Pacific Standard or Daylight Time
YST or YDT	Yukon Standard or Daylight Time

Example

Here’s an example:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT
    NEW_TIME(
        TO_DATE('2030-03-01 12:30:45', 'YYYY-MM-DD HH24:MI:SS'), 
        'AST', 
        'PST'
        )
FROM DUAL;

Result:

2030-03-01 08:30:45

As mentioned, when using this function the NLS_DATE_FORMAT parameter needs to be set to display 24-hour time, so that’s what the first line does in the above example. You can omit this line if your session’s NLS_DATE_FORMAT parameter already uses 24-hour time. See How to Set your Session’s Date Format for an more examples of setting the date format for the current session.

Null Arguments

If any argument is null, the result is null:

SET NULL 'null';

SELECT
    NEW_TIME(null, 'AST', 'ADT') AS "1",
    NEW_TIME(TO_DATE('2030-03-01 12:30:45', 'YYYY-MM-DD HH24:MI:SS'), null, 'ADT') AS "2",
    NEW_TIME(TO_DATE('2030-03-01 12:30:45', 'YYYY-MM-DD HH24:MI:SS'), 'AST', null) AS "3"
FROM DUAL;

Result:

      1       2       3 
_______ _______ _______ 
null    null    null   

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.

Missing Argument

Calling NEW_TIME() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT NEW_TIME()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NEW_TIME()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: