Oracle Convert TIMESTAMP with Timezone to DATE

by

to_timestamp_tz() function with at time zone clause can be used to convert your string literal to a value of timestamp with time zone data type: 

SQL> with t1(tm) as(
  2    select '2013-11-08T10:11:31+02:00' from dual
  3  )
  4  select to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM')
  5           at time zone '+4:00'         as this_way
  6       , to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM')
  7           at time zone 'Europe/Moscow' as or_this_way
  8    from t1
  9  /

Result:

THIS_WAY                            OR_THIS_WAY
----------------------------------------------------------------------------
2013-11-08 12.11.31 PM +04:00       2013-11-08 12.11.31 PM EUROPE/MOSCOW

And then, we use cast() function to produce a value of date data type:

with t1(tm) as(
  select '2013-11-08T10:11:31+02:00' from dual
)
select cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 
         at time zone '+4:00' as date)   as this_way  
     , cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 
         at time zone 'Europe/Moscow' as date) as or_this_way
  from t1

This_Way             Or_This_Way 
------------------------------------------
2013-11-08 12:11:31  2013-11-08 12:11:31

Find out more about at time zone clause and to_timestamp_tz() function.

More Related Contents:

  1. Current date in Oracle Db
  2. Oracle’s default DATE format
  3. Get month name from date in Oracle
  4. Check if current date is between two dates Oracle SQL
  5. Comparing with date in Oracle sql
  6. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  7. Return number from Oracle Select statement after parsing date
  8. Subtracting Dates in Oracle – Number or Interval Datatype?
  9. SQL Query to concatenate column values from multiple rows in Oracle
  10. Search All Fields In All Tables For A Specific Value (Oracle)
  11. Paging with Oracle
  12. Oracle Differences between NVL and Coalesce
  13. ORA-12560: TNS:protocol adaptor error
  14. Calculating difference between two timestamps in Oracle in milliseconds
  15. How to store only time; not date and time?
  16. How does GROUP BY work?
  17. How can I find which tables reference a given table in Oracle SQL Developer?
  18. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  19. Oracle equivalent of Postgres’ DISTINCT ON?
  20. How to get the last row of an Oracle table
  21. What is the difference between ‘YYYY’ and ‘RRRR’ in Oracle SQL
  22. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  23. PLSQL Insert into with subquery and returning clause
  24. How to populate calendar table in Oracle?
  25. Oracle 11g – Check constraint with RegEx
  26. ORACLE SQL:Get all integers between two numbers
  27. The network adapter could not establish the connection – Oracle 11g
  28. Oracle date “Between” Query
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment