How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?

by

to_date converts the input to a DATE type which does not support fractional seconds. To use fractional seconds you need to use a TIMESTAMP type which is created when using to_timestamp

pst’s comment about the ff3 modifier is also correct.

“Constant” values in the format mask need to be enclosed in double quote

So the final statement is:

select to_timestamp('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')
from dual;

More Related Contents:

  1. Oracle: how to add minutes to a timestamp?
  2. How are dates stored in Oracle?
  3. Using date in a check constraint, Oracle
  4. Formatting DATE in oracle
  5. How to find difference b/w TIMESTAMP format values in Oracle?
  6. Convert epoch to date in sqlplus / Oracle
  7. Valid Date Checks in Oracle
  8. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  9. How to get the week day name from a date?
  10. Oracle Convert TIMESTAMP with Timezone to DATE
  11. Display which day it is? sunday or monday “php coding”? [closed]
  12. Calculate difference between 2 date / times in Oracle SQL
  13. Auto Increment for Oracle
  14. Deploying and Configuring ODP.NET to work without installation with Entity Framework
  15. Add a auto increment primary key to existing table in oracle [duplicate]
  16. ways to avoid global temp tables in oracle
  17. Spring Batch ORA-08177: can’t serialize access for this transaction when running single job, SERIALIZED isolation level
  18. In Ruby on Rails, what’s the difference between DateTime, Timestamp, Time and Date?
  19. How to generate an entity-relationship (ER) diagram using Oracle SQL Developer
  20. String to date in Oracle with milliseconds
  21. Create Pivot view in SQL from a SQL table
  22. Oracle.ManagedDataAccess.EntityFramework – ORA-01918: user ‘dbo’ does not exist
  23. Why doesn’t PL/SQL respect privileges granted by Roles?
  24. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
  25. Speed up to_sql() when writing Pandas DataFrame to Oracle database using SqlAlchemy and cx_Oracle
  26. What is the algorithm used by the ORA_HASH function?
  27. passing in table name as plsql parameter
  28. automating rollback script oracle
  29. How do I get column datatype in Oracle with PL-SQL with low privileges?
  30. How to View Oracle Stored Procedure using SQLPlus?

Leave a Comment