Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dates and Random Numbers in PL/SQL?
Jason C Miller wrote:
>
> Hi,
>
> I'm starting my first sizable effort in PL/SQL programming and I'm
> stuck against two small walls. (My configuration is Oracle 7.3 and
> Oracle Webserver Cartridges).
>
> One thing I want to do is easily work with dates. I've seen functions
> to change the date, but not times (except for the time zone stuff). I'd
> like to write a function that does the following:
> new_date := old_date + hours + minutes;
> Ie. I'd like to take a date and add an hour and/or minutes to it. I
> realize I could do this by converting it to a character string and doing
> all kinds of manipulations with it-- is there an easier way?
>
> <snip>
>
Hi Jason,
here is something to your date arithmetics question:
Add 1 day to system date: sysdate + 1 Add 1 hour to system date: sysdate + 1/24 Add 1 minute ---- " -----: sysdate + 1/(24*60)
Unfortunately this doesn't handle DST changes. Suppose summertime begins (Finnish
timezone) 30-Mar-1997 03:00 when clock is turned to be 30-Mar-1997 04:00. The
following query gives wrong result (there is no 03:30 in Finnish timezone in given
day):
SQL> alter session set nls_date_format='YYYY.MM.DD HH24:MI:SS';
SQL> select to_date('1997.03.30 02:30:00')+1/24 from dual;
TO_DATE('1997.03.30
Usually this is not a big issue and could be solved by storing critical date-columns in UTC-time.
-- Timo Haatainen Carelcomp Forest OyReceived on Wed Jun 04 1997 - 00:00:00 CDT