Re: Date and time calculations

From: Michael Segulja <msegulja_at_yahoo.com>
Date: Tue, 06 Aug 2002 14:44:57 GMT
Message-ID: <pan.2002.08.06.09.41.04.999341.3744@yahoo.com>

Thanks everybody for your help. I new it was simple.

Thanks again.
Michael

On Wed, 31 Jul 2002 16:32:04 -0500, Thomas Gaines wrote:

> <!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html>
> Michael -
> When doing math, you need to make certain that the values that
> you're manipulating make sense.  Subtracting a number from
> another number is okay.  Subtracting a date from another date
> is also okay.
> Subtracting a character string from another character string is
> generally not okay unless those characters can be cast to numbers.
> As an example, try this:
> <tt>SQL> select '3' - '2' from
> dual;</tt><tt></tt> <tt><font
> color="#FF0000">   '3'-'2'</tt> <tt><font
> color="#FF0000">----------</tt> <tt><font
> color="#FF0000">        
> 1</tt><tt></tt> Then try this:
> <tt>SQL> select 'tom' - 'david' from
> dual;</tt> <tt>select 'tom' - 'david'
> from dual</tt> <tt><font
> color="#FF0000">       *</tt>
> <tt>ERROR at line 1:</tt> <tt><font
> color="#FF0000">ORA-01722: invalid number</tt> In your case,
> you'll have two character strings with colons embedded in them, and
> those are the cause of your error.  You could do this (note the
> absence of colons):
> <tt>select to_char(timestop, 'HH24MISS') -
> to_char(timestart, 'HH24MISS')</tt> <tt><font
> color="#FF0000">as total from timeclock;</tt> but the result
> would be pretty meaningless.  Try it yourself if you're unsure.
> If you're looking for time passage, simply subtract the two date
> fields without doing a cast:
> <tt>select timestop - timestart as total from
> timeclock;</tt>
> The result will be in the number of days, and it shouldn't be too
> difficult to get this difference in other units. For fun, do
> this:
> <tt>alter session set nls_date_format =
> 'mm/dd/yyyy hh24:mi:ss';</tt>
> <tt>select sysdate, sysdate + 10 from
> dual;</tt> What do you get? Bye,
> Tom
>  
> Michael Segulja wrote:
> <blockquote TYPE=CITE>I'm sure this is a very simple question, but I'm
> having trouble for some
> reason getting the sql correct.
> I have a table called timeclock that very simplay has these fields:
>         id
>         timestart
>         timestop
>         timetotal The value of
> id is 1 and will be the only row in the table.  I can then
> update the table for the start time, and then several hours later
> update the table with the stop time.  I get the values from
> sysdate.  I can then
> view the table with this:
> select to_char(timestop, 'HH24:MI:SS') AS stop, to_char(timestart,
> 'HH24:MI:SS') AS start from timeclock; I want to subtract
> timestop from timestart, and get the total time worked, so I wrote a
> query like this: select to_char(timestop, 'HH24:MI:SS') -
> to_char(timestart, 'HH24:MI:SS') as total from timeclock;
> But it doesn't work.  I get this error: select
> to_char(timestop, 'HH24:MI:SS') - to_char(timestart, 'HH24:MI:SS') AS
> total
>        * ERROR at line 1:
> ORA-01722: invalid number
> I'm this is an extreme beginners mistake.  Can anybody tell me
> what I
> need to do to get this right?
> Thanks,
> Michael</blockquote>
>
>  </html>
Received on Tue Aug 06 2002 - 09:44:57 CDT