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.

If someone can give me any advice I will be grateful. Thanks,

Diego Buendia
Barcelona, Spain

> 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

Diego Buendia wrote:
>
> Hello:
>
> 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:

Oracle supports a DATE datatype that stores "YYYY-MM-DD HH:MM:SS"
(without punctuation) and a huge set of 'masks' that can map just about
anything you can throw at it to go from a 'character equivalent' to the
DATE type and back.

You don't mention the version of Oracle. This can be important as some
things have changed, such as defaults, over the years.

Assuming your project is planning on staying supported for the next
reasonable time frame (as old versions are at or nearing end-of-life),
you are hopefully using Oracle9i Release 2 (version 9.2.0.4).

In this case, the trail to the relevant (and free) docco is:

http://otn.oracle.com
>> Documentation (icon at top of page) = http://otn.oracle.com/documentation/index.html
>> Oracle9i Database Release 2 = http://otn.oracle.com/pls/db92/db92.homepage
>> List of Books = http://otn.oracle.com/pls/db92/db92....emark=homepage
>> SQL Reference
then go to
>> "Chapter 2"
to get the general definition of the DATE datatype (and defaults)
and "format models" used in conversion
and
>> "Chapter 6"
to get the functions available for date manipulation and conversion

>
> 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

-- why?? because the two RDBMSs are not the same, and there is
(probably) no standard definition of the default.

-- how to handle this? conversion functions will likely be your friend
as well as the format models. Specifically look at the 'DAYS_BETWEEN'
function (and remember that is can handle fractional dates).

HTH
/Hans

Thank you, Rene. Beginnings are difficult. I've found this solution
from your suggest:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI'

and then

INSERT INTO Tb_Schedules (dt_1, dt_2) VALUES ('1-ENE-2004 08:00',
'1-ENE-2004 20:00' )

It works also with the mask you proposed. Thanks again and see you!

Barcelona, Spain

>
>
> 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