RE: Date math and TRUNC(SYSDATE).

TRUNC (Oracle 8 Documentation) can either be a number function or a date function. When the parameter is a DATE datatype, the function is supposed to return a DATE. The environment where the SQL was executed may have made a difference. Regardless, since the function can either return a number or date my guess is that the Oracle database experience a brief "sometimer's" disease and got a little confused. Is TIMESTAMP# a DATE column?

Tom Harleman
11080 Willowmere Dr.
Indianapolis, IN 46280
317-844-2884 Home
317-843-9122 Home Office

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jesse, Rich Sent: Tuesday, August 01, 2000 9:47 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Date math and TRUNC(SYSDATE).

O.K., now I'm *really* confused. I run both scenarios today to show a co-worker, and they work the same.

I guess I'll chalk this one up to a case of the Mondays...

Bleah.

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             QTI -- Sussex, WI USA

> -----Original Message-----
> From: Jesse, Rich
> Sent: Monday, July 31, 2000 18:25
> To: Multiple recipients of list ORACLE-L
> Subject: Date math and TRUNC(SYSDATE).
>
>
> So, there I was, cleaning up the usual garbage out of SYS.AUD$ when I
> decided to check my work. Hmmm...instead of deleting
> garbage older than a
> week, it was only deleting rows older than 10 days. Here's a snip:
>
> DELETE FROM AUD$
> WHERE TIMESTAMP# < TRUNC(SYSDATE) - 7
> AND ACTION# = 85
> AND USERID = 'JOE_DB_USER'
> AND SPARE1 = 'joeosusr';
>
> I remedied the problem by moving the parens of the TRUNC to
> include the "7"
> days, like this:
>
> WHERE TIMESTAMP# < TRUNC(SYSDATE - 7)
>
> ...and all seems well. Now, I just need to know why! My
> guess is that I
> was running into some typecasting problems with the TRUNC'd
> date subtracting
> 7, but I couldn't seem to find any info on it anywhere.
>
> Any takers?
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com QTI -- Sussex, WI USA
>
>
> --------------------------------------------------------------
> ----------
>
> This message has been scanned for viruses with Trend Micro's
> Interscan VirusWall.
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

This message has been scanned for viruses with Trend Micro's Interscan VirusWall.

--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may