Re: Brain freeze doing Date comparisons

Okay, I think my brain is finally starting to thaw.

It appears that I've been able to accomplish my testing (on whether I
need to copy my clob or not) with the following statement:
      SELECT   'N'
        INTO    v_needed
        FROM   edit_backups
       WHERE   ( EXTRACT (DAY FROM SYSTIMESTAMP - update_date) <= 0
                AND EXTRACT (HOUR FROM SYSTIMESTAMP - update_date) <= 0);

(I default v_needed to 'Y' when I declare it)

Then I do an IF statement of:
IF v_needed = 'Y'  then <perform my insert>

I had to add the extra extract statement for days, as an entry made
yesterday at roughly the same time was appearing as 1 day and 0 hours.
It also appears the difference of subtracting my update_date from
systimestamp gets reported as solely days, hours, etc., no months or
years.

Hope this helps others in the future.

-- 
-- Bill Ferguson
--
http://www.freelists.org/webpage/oracle-l

Other related posts: