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

Harry331 wrote:
[snip]
> 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 ...
> ?

I restarted the sql*plus and re-ran this SQL OK within a couple of seconds.
But the result was that all 3 records have the same hour/minutes values. Received on Fri Mar 03 2006 - 14:42:24 CST