Re: Brain freeze doing Date comparisons

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