Re: update a date field with fixed date but random hours/minutes

"Harry331" <harryooopotter_at_hotmail.com> wrote in message news:1141412997.576909.165100_at_i40g2000cwc.googlegroups.com...

Michel Cadot wrote:

> "Harry331" <harryooopotter_at_hotmail.com> a écrit dans le message de news:
> 1141410442.740870.319880_at_t39g2000cwt.googlegroups.com...
> | But I'm not sure how to come up with the final SQL to update Exam_Date
> | so that each record will have a different (and random) hh24:mi value.
> |
> | Any help appreciated.
> |

> Post what you've done and what seems wrong to you
> and we help you to complete.

The following SQL changed the exam_date to yesterday's date, but the hours/minutes
are all the same value (equal to that when I ran the SQL). What I want is to randonize the hours/minutes values.

update study s
  set s.my_exam_date_time = sysdate -1
where
  s.my_study_id in
(
select
  s.my_study_id as Study_ID
from
  study s
where
  to_char(s.my_exam_date_time, 'YYYY-MM-DD') >= '2006-01-01'   and to_char(s.my_exam_date_time, 'YYYY-MM-DD') <= '2006-02-28' )
;

convert the sysdate-1 date (not time) values with to_char, append the results or your random hours and minutes, then convert the resulting character expression back to a date with to_date

presumably dbms_random will be invoked twice for each row so you will get random hours and random minutes. if for some reason that doesn't work, repost, you might need to update some sort of a correlated subquery

++ mcs Received on Fri Mar 03 2006 - 13:33:46 CST