Re: Brain freeze doing Date comparisons

From: Bill Ferguson <>
Date: Thu, 26 Feb 2009 10:45:14 -0700
Message-ID: <>

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.


Received on Thu Feb 26 2009 - 11:45:14 CST

Original text of this message