Re: Brain freeze doing Date comparisons

From: Yechiel Adar <adar666_at_inter.net.il>
Date: Sun, 01 Mar 2009 11:22:20 +0200
Message-id: <49AA53CC.1090508_at_inter.net.il>

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.

Adar Yechiel
Rechovot, Israel

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
> years.
>
> Hope this helps others in the future.
>
>

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

Received on Sun Mar 01 2009 - 03:22:20 CST