Re: Time format subtraction

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 6 Jan 2007 15:44:25 +0100
Message-ID: <459fb5ca$0$285$426a74cc@news.free.fr>

<i5ha_at_yahoo.com> a écrit dans le message de news: 1168092886.352706.151340_at_i15g2000cwa.googlegroups.com...
| Hi everyone,
| I'm trying to subtract some times so as to get the average time
| interval for a job, but the problem is that my database has the times
| stored in the formats as shown here: 11:00:00AM, 9:00:00PM, 5:30:00PM
| and so on. How can I subtract these times? I can't do it directly, i.e
| "Select to_timestamp(Session_start) - To_timestamp(Session_end) from
| session" because I'm getting error messages due to the 'AM' and 'PM'
| found in the times(I think). So I thought of a cursor (again...) that
| would add 12hours to the PM times, and then I could delete the 'AM' and
| 'PM' strings from my table and do my calculation, but then I'm having
| error messages. My cursor is displayed below but is there a simpler
| method to do this? Please advice.
|
| Declare
| Cursor c_stime is
| Select Session_start from session where Session_start LIKE
| '________PM' or Session_start LIKE '_______PM';
|
| Begin
| For everyrec in c_stime Loop
| Update session set Session_start = To_timestamp(everyrec.Session_start,
| 'HH24:MI:SS') + interval '+00 12:00:00' day to second;
| End loop;
| Commit;
| End;
|
| ERROR at line 1:
| ORA-01830: date format picture ends before converting entire input
| string
| ORA-06512: at line 7
|
| Thaanks
|

SQL> select to_char(to_timestamp('07:04:25PM','HH:MI:SSAM'),'HH24:MI:SS')   2 from dual;
TO_CHAR(

---

19:04:25

1 row selected.

SQL> select to_char(to_timestamp('07:04:25AM','HH:MI:SSAM')

  2                 + interval '+00 12:00:00' day to second,
  3                 'HH24:MI:SS')

  4 from dual;
TO_CHAR(

---

19:04:25

1 row selected.

Have a look at "Datetime Format Models" section in SQL Reference.

Regards
Michel Cadot Received on Sat Jan 06 2007 - 08:44:25 CST