Re: update hour in date field

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