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. Received on Mon Jul 31 2000 - 17:16:39 CDT