Re: DATE manipulation problem

On Mon, 2 Dec 2002 23:45:41 -0500, "Dave Harris" <daharris_at_erinet.com> wrote:

> I just completed my first class in Oracle and for my final project I built
>a music CD database. I have a table called "songs" which has the song title
>and the running time (among other fields) and a table called "album" which
>links to the "songs" table. I wanted to do a report or a query which would
>add up all of the running times (which are declared as DATE datatype) for
>all of the albums in the database and give me a total running time for the
>each album.
> The teacher suggested I go back and replace the DATE running times with
>numeric values as he believes you can't manipulate the DATE datatypes. I was
>wondering if you could somehow convert the running time to a string then use
>a substring function to pull out the minutes and seconds values to use in
>the calculation then somehow put the final calculation back into a time
>format.
> I guess this is a long winded way of asking if the string conversion can
>work. Even though the class is over I still want to incorporate this in my
>database program. Any ideas and advice would be greatly appreciated. Thanks
>in advance!!!!
>
>Dave Harris
>daharris_at_erinet.com

I would
- use the same dummy date for all running times (the hours, minutes and seconds of course are real)
- use 60 * to_number(to_char(<running time>,'mi')) + to_number(to_char(<running time>,'ss')) to get the duration in seconds - that should do it.

Hth

>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Dec 02 2002 - 23:46:49 CST