format interval with to_char

by

you could cast the result if you want less precision:

SQL> SELECT TO_DSINTERVAL('10 10:00:00') t_interval FROM dual;

T_INTERVAL
-----------------------------------------------------------
+000000010 10:00:00.000000000

SQL> SELECT CAST(TO_DSINTERVAL('10 10:00:00')
  2                 AS INTERVAL DAY(2) TO SECOND(3)) t_interval
  3    FROM dual;

T_INTERVAL
-----------------------------------------------------------
+10 10:00:00.000

Edit following OP comment:

From The Oracle Documentation (11gr1):

Interval datatypes do not have format models. Therefore, to adjust their presentation, you must combine character functions such as EXTRACT and concatenate the components.

It seems you will have to manually use EXTRACT to achieve the desired output:

SQL> SELECT to_char(extract(DAY FROM t_interval), 'fmS99999') || ' ' ||
  2         to_char(extract(HOUR FROM t_interval), 'fm00') || ':' ||
  3         to_char(extract(MINUTE FROM t_interval), 'fm00') || ':' ||
  4         to_char(extract(SECOND FROM t_interval), 'fm00.000')
  5    FROM (SELECT TO_DSINTERVAL('10 01:02:55.895') t_interval FROM dual)
  6  ;

TO_CHAR(EXTRACT(DAYFROMT_INTER
------------------------------
+10 01:02:55.895

This is not very elegant but it seems it is the only way to deal with microseconds precision.

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. I want to find out how much zero come after 1 in SQL
  3. Splitting string into multiple rows in Oracle
  4. GROUP BY with MAX(DATE) [duplicate]
  5. How to use the ‘as’ keyword to alias a table in Oracle?
  6. Can a foreign key be NULL and/or duplicate?
  7. Stored Procedure with optional “WHERE” parameters
  8. subquery in FROM must have an alias
  9. SQL multiple columns in IN clause
  10. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  11. How can I select from list of values in Oracle
  12. Alternatives to LIMIT and OFFSET for paging in Oracle [duplicate]
  13. How to get second largest or third largest entry from a table [duplicate]
  14. Split String by delimiter position using oracle SQL
  15. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  16. ORA 00904 Error:Invalid Identifier
  17. Update with Join query in Oracle
  18. How to select records with maximum values in two columns?
  19. What does the colon sign “:” do in a SQL query?
  20. Autoincrement in oracle to already created table
  21. SQL not displaying null values on a not equals query?
  22. What can cause an Oracle ROWID to change?
  23. How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
  24. Oracle query to match all values in the list among all rows in table
  25. Inserting into Oracle and retrieving the generated sequence ID
  26. adding primary key to sql view
  27. How do I call an Oracle stored procedure from an Excel VBA script?
  28. Oracle DB: How can I write query ignoring case?
  29. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment