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

"Harry331" <harryooopotter_at_hotmail.com> a écrit dans le message de news: 1141410442.740870.319880_at_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.
|

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

Regards
Michel Cadot Received on Fri Mar 03 2006 - 12:42:58 CST