Re: Time range calculations

From: Hans Forbrich <hforbric_at_yahoo.net>
Date: Wed, 14 Jan 2004 00:31:46 GMT
Message-ID: <40048D6B.9349F5CB@yahoo.net>

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.docindex?remark=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 Received on Tue Jan 13 2004 - 18:31:46 CST