Re: Brain freeze doing Date comparisons
Date: Sun, 01 Mar 2009 11:22:20 +0200
You can use (unless I am missing something): where update_date > to_timestamp(to_char(sysdate - MY_PERIOD ,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS')); MY_PERIOD - the period you want to check.
I think that your solution will have problem when run after midnight, 00:15, because
EXTRACT (DAY FROM SYSTIMESTAMP - update_date)
might return 1.
Bill Ferguson wrote:
> 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
> Hope this helps others in the future.