Re: Time range calculations

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 13 Jan 2004 23:44:30 GMT
Message-ID: <bu1vsu$cr7c9$1@ID-82536.news.uni-berlin.de>

> Hello:
>
> Coming from 5 years of SQL Server development and administration, I am
> now in a Oracle development project, so I'm studying and trying a lot
> of new things. It's an exciting thing to learn.
>
> My preliminar impression as developer is that Oracle seems more
> powerful than SQL Server, and the PL/SQL language is more advanced and
> modern than T/SQL, but on the other hand all the Oracle thing is
> slower on Windows machines and maybe for a medium business with no
> cross-platform issues SQL Server is easier to get with. Well, I don't
> feel too much incomfortable, you know, as with the programming
> languages, having learned one you have got much of all the others.
>
> The thing I am confused right now is with the DATE type of data. I am
> using tables with scheduling data that define the working day for
> several types of people. Some of then get into their job at 8:00am and
> get out at 20:00am, and other have a rest at noon, having then two
> segments of working time. In SQL Server, I used to insert values for
> time-only columns:
>
> insert tb_schedule values ( 1, 1, 'L', '08:00', '20:00' )
>
> and, as SQL Server defaults the date part to 1/1/1900, the differences
> between times were consistents. Now, Oracle defaults to the first day
> of the current month (why?!) so if I update a date in the future I
> won't have the figured result, as the month will be shifted if I don't
> apply the corresponding workaround. Moreover, yet I haven't found what
> the simplest syntax to apply to get an equivalent to the former SQL
> Server sentence.
 

Diego

Try a modification of

select
 to_char(

    trunc (sysdate) +

      (dt_2-trunc(dt_2)) - 
      (dt_1-trunc(dt_1)),

    'hh24:mi:ss'
  )
from
  tb_schedule;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Tue Jan 13 2004 - 17:44:30 CST