format interval with to_char

sql oracle intervals
Georg Leber picture Georg Leber · Jun 9, 2009 · Viewed 27.9k times · Source

Following SQL command

select TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))) from table1

produces a result of the format: +000000000 00:03:01.954000.

Is it possible to enter a special format in the to_char function in order to get a result of format: +00 00:00:00.000?

Answer

Vincent Malgrat picture Vincent Malgrat · Jun 9, 2009

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More