Convert String ISO-8601 date to oracle’s timestamp datatype

by

The date format model elements are listed in the Datetime Format Models documentation:

SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
FROM DUAL

TO_TIMESTAMP_TZ('2014-09-12T11:53:06+00:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-SEP-14 11.53.06.000000000 +00:00

The fixed T can be included as a character literal:

You can include these characters in a date format model:

  • Punctuation such as hyphens, slashes, commas, periods, and colons
  • Character literals, enclosed in double quotation marks

TZH is tome zone hour, and TZM is time zone minutes. The rest are more common model elements.

More Related Contents:

  1. Can we update primary key values of a table?
  2. Oracle (ORA-02270) : no matching unique or primary key for this column-list error
  3. How to display table data more clearly in oracle sqlplus
  4. Liquibase lock – reasons?
  5. How SID is different from Service name in Oracle tnsnames.ora
  6. Are foreign keys really necessary in a database design?
  7. Is it possible to generate django models from the database?
  8. What is a View in Oracle?
  9. What is the difference between Non-Repeatable Read and Phantom Read?
  10. Business Logic: Database or Application Layer
  11. What is the default Precision and Scale for a Number in Oracle?
  12. Oracle: How do I convert hex to decimal in Oracle SQL?
  13. Display dynamic image from database or remote source with p:graphicImage and StreamedContent
  14. What are the performance characteristics of sqlite with very large database files? [closed]
  15. Primary keys with Apache Spark
  16. What are database normal forms and can you give examples? [closed]
  17. Exposing database IDs – security risk?
  18. How can I put a database under git (version control)?
  19. How to use mongoimport to import csv
  20. Best way to test a MS Access application?
  21. How to backup sqlite database?
  22. Saving images: files or blobs?
  23. Still Confused About Identifying vs. Non-Identifying Relationships
  24. Normal forms – 2nd vs 3rd – is the difference just composite keys? non trivial dependency?
  25. Is there any NoSQL data store that is ACID compliant?
  26. I need a client side browser database. What are my options [closed]
  27. Android: Where are database files stored?
  28. How to get item count from DynamoDB?
  29. How to find SQLITE database file version
  30. Query a Table’s Foreign Key relationships

Leave a Comment