Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: update hour in date field

Re: update hour in date field

From: Ed Prochak <edprochak_at_gmail.com>
Date: Mon, 17 Sep 2007 17:10:41 -0000
Message-ID: <1190049041.610321.14170@o80g2000hse.googlegroups.com>


On Sep 17, 1:16 am, h_..._at_hotmail.com wrote:
> Please disregard this request below. I do not need it. Thanks.
>
> On Sep 17, 1:02 am, h_..._at_hotmail.com wrote:
>
>
>
> > Hello,
> > I need to update the hour part of a date field, say the field name is
> > createddate.
>
> > Like if its value is 17-SEP-2007 21:00:00, I need to update it as 17-
> > SEP-2007 20:00:00
>
> > and if its value is 17-SEP-2007 22:40:00, then it be updated as
> > 21:40:00
>
> > In other terms need to reduce the hour field value by 1 for all rows
> > in the table.
>
> > Please advise. I am using Oracle 9i. Thank you very much.

I realize you do not need it, but in case some lurker is interested:

Dates in Oracle are stored in units of DAYS. so for example 17-SEP-2007 22:40:00 plus 1 yields 18-SEP-2007 22:40:00 the hours. minutes and seconds are stored a a fractional part of the date
Since there are 24 hours per day, one hour is 1/24 part of a day. So, to increment the time by 1 hour just add 1/24. and similar fractions for
minutes and seconds. So to add 37minutes use 37/(24*60).

Hope that helps someone, even if you solved it on your own.

   Ed Received on Mon Sep 17 2007 - 12:10:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US