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

From: Harry331 <harryooopotter_at_hotmail.com>
Date: 3 Mar 2006 10:27:22 -0800
Message-ID: <1141410442.740870.319880@t39g2000cwt.googlegroups.com>

I have an SQL which returns something like these below. Re: Oracle 9i on Windows 2000 server.

select
  s.my_study_id as Study_ID,
  to_char(s.my_exam_date_time, 'YYYY-MM-DD hh24:mi') as exam_date 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';

Study_ID Exam_Date

1000001	  2006-01-25 08:50
1000011	  2006-02-18 08:50
1000012	  2006-02-22 08:50

What I want to do next, is to update the Exam_Date field so that the values become yesterday's date, but the time would be random.

i.e.
Study_ID Exam_Date

1000001	  2006-03-02 05:23
1000011	  2006-03-02 14:07
1000012	  2006-03-02 18:36

I've looked up the way to generate a random #.
- for Hour (i.e. hh24):

  select trunc(DBMS_RANDOM.VALUE(0, 24)) from dual;

- for minutes (i.e. mi):

  select trunc(DBMS_RANDOM.VALUE(0, 60)) from dual;

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. Received on Fri Mar 03 2006 - 12:27:22 CST