Home » SQL & PL/SQL » SQL & PL/SQL » to_char(interval) (Windows 10.2.0.1)
to_char(interval) [message #432807] Thu, 26 November 2009 06:46 Go to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Is it possible to format an interval, TO_CHAR doesn't work/give the expected result to me !?
SELECT TO_CHAR( INTERVAL '170000.12345' SECOND, 'HH:MI:SS,FF') interv FROM DUAL;

interv
-------------------
+01 23:13:20.123450
Re: to_char(interval) [message #432811 is a reply to message #432807] Thu, 26 November 2009 06:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks ok to me.
What were you expecting to see?
Re: to_char(interval) [message #432814 is a reply to message #432807] Thu, 26 November 2009 07:17 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
The format string seem's not to influence the output.
Expected 2 fractional seconds ('FF') got 6 ('123450'), can't change the format of the interval days '+01' to '+001'.

[Updated on: Thu, 26 November 2009 07:18]

Report message to a moderator

Re: to_char(interval) [message #432816 is a reply to message #432814] Thu, 26 November 2009 07:26 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Is this you are looking for ?

SQL> ed
Wrote file afiedt.buf

  1  SELECT CAST(TO_DSINTERVAL( TO_CHAR( INTERVAL '170000.12345' SECOND, 'HH:MI:SS,FF'))
  2                    AS INTERVAL DAY(3) TO SECOND(2)) t_interval
  3*      FROM dual
SQL> /

T_INTERVAL
---------------------------------------------------------------------------
+001 23:13:20.12

SQL>


SQL> ed
Wrote file afiedt.buf

  1  WITH t AS (select INTERVAL '170000.12345' SECOND AS i FROM dual)
  2  SELECT CAST(i AS INTERVAL DAY(3) TO SECOND(2)) AS formatted
  3* FROM t
SQL> /

FORMATTED
---------------------------------------------------------------------------
+001 23:13:20.12

SQL>



sriram

[Updated on: Thu, 26 November 2009 07:38]

Report message to a moderator

Re: to_char(interval) [message #432819 is a reply to message #432816] Thu, 26 November 2009 07:38 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
@ramoradba, unfortunately can't confirm :
SELECT CAST(TO_DSINTERVAL(TO_CHAR( INTERVAL '170000.12345' SECOND,'HH:MI:SS,FF')) 
             AS INTERVAL DAY(3)TO SECOND(2)) t_interval
  FROM DUAL;
t_interval
--------------------
+01 23:13:20.123450

The same as f.e.:
SELECT CAST(TO_DSINTERVAL          (INTERVAL '170000.12345' SECOND)               
             AS INTERVAL DAY(7) TO SECOND(4)) t_interval
   FROM dual;
t_interval
--------------------
+01 23:13:20.123450

Re: to_char(interval) [message #432820 is a reply to message #432819] Thu, 26 November 2009 07:40 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> ed
Wrote file afiedt.buf

  1  WITH t AS (select INTERVAL '170000.12345' SECOND AS i FROM dual)
  2  SELECT CAST(i AS INTERVAL DAY(7) TO SECOND(4)) AS formatted
  3* FROM t
SQL> /

FORMATTED
---------------------------------------------------------------------------
+0000001 23:13:20.1235



Sriram Smile
Re: to_char(interval) [message #432822 is a reply to message #432820] Thu, 26 November 2009 07:43 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Nope - the same, seems to be a problem in my ORACLE 10.2.0.1.
Re: to_char(interval) [message #432823 is a reply to message #432822] Thu, 26 November 2009 07:44 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>


sriram Smile
Re: to_char(interval) [message #432824 is a reply to message #432823] Thu, 26 November 2009 07:46 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
select banner from v$version;

BANNER                                                          
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production                          
CORE	10.2.0.1.0	Production                                      
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production         
NLSRTL Version 10.2.0.1.0 - Production                          

_jum Shocked


Re: to_char(interval) [message #432825 is a reply to message #432814] Thu, 26 November 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format is checked but ignored in TO_CHAR when parameter is of INTERVAL type:
SQL> SELECT TO_CHAR(INTERVAL '170000.12345' SECOND, 'Day MONTH YYYY') FROM DUAL;
TO_CHAR(INTERVAL'17
-------------------
+01 23:13:20.123450

1 row selected.

Regards
Michel
Re: to_char(interval) [message #432826 is a reply to message #432824] Thu, 26 November 2009 07:51 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Mine is 32 bit...Sorry i cant help you...Right now i dont have have that installed at this testing node...

This may help you

Rewrite based on your requirement..

SQL> ed
Wrote file afiedt.buf

  1  SELECT to_char(extract(DAY FROM t_interval), 's099') || ' ' ||
  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.00')
  5*      FROM (SELECT TO_DSINTERVAL( TO_CHAR( INTERVAL '170000.12345' SECOND, 'HH:MI:SS,FF')) t_interval FROM dual)
SQL> /

TO_CHAR(EXTRACT(DAY
-------------------
+001 23:13:20.12



Sriram Smile

[Updated on: Thu, 26 November 2009 08:17]

Report message to a moderator

Re: to_char(interval) [message #432827 is a reply to message #432826] Thu, 26 November 2009 08:17 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
Thanks a lot Sriram, so I have to code some bit for my 32bit better system Wink
Previous Topic: Copy/Ammend/Insert Row based on ID
Next Topic: Trigger Help
Goto Forum:
  


Current Time: Sun Dec 11 08:11:15 CST 2016

Total time taken to generate the page: 0.19283 seconds