Re: Subtract Hours From A Given Date

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 10 Sep 1999 23:26:52 +0100
Message-ID: <937003226.911.0.nnrp-01.9e984b29@news.demon.co.uk>

Nice little trick, but doesn't work properly.

Try it at 4 am, and the last 5 digits of the number are 92,800 - since there are only 86,400 seconds in a day you get ORA-01853

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Kenneth C Stahl wrote in message <37D900FD.B246A25B_at_Unforgettable.com>...
>Harvey wrote:
>
>> Does anyone know how to subtract hours from a given datetime in Oracle?
>>
>> Is there a function similar to add_months?...ie ADD_MONTHS(sysdate,-6)
will
>> return a date 6 months before the current date.
>
>To subtract 5 hours:
>
>select to_char(sysdate,'YYYYMMDD HH24:MI:SS'),
> to_char(to_date(to_number(to_char(sysdate,
>

                                                                'JSSSSS')) -


>                                  (60 * 60 * 5),'JSSSSS'),'YYYYMMDD

HH24:MI:SS')
>
>from dual;
>
Received on Fri Sep 10 1999 - 17:26:52 CDT