Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions / Oracle NEW_TIME

Oracle NEW_TIME

The Oracle NEW_TIME() function converts a date from a time zone to another.

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

Syntax

The following illustrates the syntax of Oracle NEW_TIME() function:

NEW_TIME(date, from_timezone, to_timezone)
Code language: SQL (Structured Query Language) (sql)

Arguments

The NEW_TIME() function accepts three arguments

1) date

A date whose time zone should be converted

2) from_timezone

A time zone of the date

3) to_timezone

A time zone to which the  date should be converted

The following table illustrates the permitted values for from_timezone and to_timezone:

Time zoneName
ADTAtlantic Daylight Time
ASTAtlantic Standard Time
BDTBering Daylight Time
BSTBering Standard Time
CDTCentral Daylight Time
CSTCentral Standard Time
EDTEastern Daylight Time
ESTEastern Standard Time
GMTGreenwich Mean Time
HDTAlaska-Hawaii Daylight Time
HSTAlaska-Hawaii Standard Time Time
MDTMountain Daylight Time
MSTMountain Standard Time
NSTNewfoundland Standard Time
PDTPacific Daylight Time
PSTPacific Standard Time
YDTYukon Daylight Time
YSTYukon Standard Time

Return value

The NEW_TIME() function returns a value of the DATE type, regardless of the date argument.

Examples

To demonstrate the NEW_TIME() function, first, we need to change the date format to 24-hour time format as follows:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Code language: SQL (Structured Query Language) (sql)

The following example shows how to convert a date from a time zone to another:

SELECT NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ), 'AST', 'PST' ) TIME_IN_PST FROM DUAL;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

06-AUG-2017 21:30:45
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle NEW_TIME() function to convert a date time value in one time zone to another.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle NEXT_DAY
Next Oracle LOCALTIMESTAMP

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.