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

From: Harry331 <harryooopotter_at_hotmail.com>
Date: 3 Mar 2006 12:21:00 -0800
Message-ID: <1141417260.685120.208030@u72g2000cwu.googlegroups.com>

Mark C. Stock wrote:

> > "Harry331"
> > | 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.
> > |

[snip]

> 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

Thanks for the hint.

Below is what I've tried so far.
But my solution (4th SQL) ran forever. What's wrong with it?

1st SQL (select the Study_ID candudates)

---

SQL> select
  2 s.my_study_id as Study_ID
  3 from
  4 study s
  5 where
  6 to_char(s.my_exam_date_time, 'YYYY-MM-DD') >= '2006-01-01'   7 and to_char(s.my_exam_date_time, 'YYYY-MM-DD') <= '2006-02-28'; 3 rows selected

Study_ID
1000001
1000011
1000012

2nd SQL (generate a date string)

---

SQL> select to_char(sysdate -1,'YYYY-MM-DD')

  2                    ||' '
  3                    ||(select trunc(dbms_random.value(0,24)) from
dual)
  4                    ||':'
  5                    ||(select trunc(dbms_random.value(0,60)) from
dual)
  6                  from dual;

1 rows selected

2006-03-02 16:43

3rd SQL (convert the date string to_date)

---

SQL> select
  2 to_date((select to_char(sysdate -1,'YYYY-MM-DD')

  3                    ||' '
  4                    ||(select trunc(dbms_random.value(0,24)) from
dual)
  5                    ||':'
  6                    ||(select trunc(dbms_random.value(0,60)) from
dual)
  7                  from dual),
  8             'YYYY-MM-DD hh24:mi')

  9 from dual;
1 rows selected

03-02-2006

4th SQL (update exam_date to be yesterday's date with random hr/mi)

---

SQL> update study s
  set s.my_exam_date_time =
   to_date((select to_char(sysdate -1,'YYYY-MM-DD')

                  ||' '
                  ||(select trunc(dbms_random.value(0,24)) from dual)
                  ||':'
                  ||(select trunc(dbms_random.value(0,60)) from dual)
                from dual),
           'YYYY-MM-DD hh24:mi')

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' )
;
 ... this SQL ran forever ...
 ? Received on Fri Mar 03 2006 - 14:21:00 CST