Re: Date comparison in SQL

The DB2/400 database (on the AS/400) takes care of this date/end-of-month adjustment for you. In article <2q5q8s$4kb@emory.mathcs.emory.edu>, alan@po.den.mmc.com (Alan Popiel) writes: |> ->From: matuscak@rohrer.com (Joe Matuscak) |> ->Subject: Date comparison in SQL |> ->Date: Mon, 02 May 1994 16:55:21 -0400 |> ->Reply-To: matuscak@rohrer.com (Joe Matuscak) |> ->Organization: Rohrer Corporation |> -> |> ->Hi. Ive got a table that is used for logging information about when a |> ->program is run. Among other things, its got a date field in it. Id like to |> ->have my nightly cron job run some SQL that basically says "delete from |> ->userlog where timestamp is older than 6 months". Can someone shed some |> ->light on how to do that? |> -> |> ->-- |> ->Joe Matuscak |> ->Rohrer Corporation |> ->717 Seville Road |> ->Wadsworth, Ohio 44281 |> ->(216)335-1541 |> ->Matuscak@Rohrer.com |> |> Hello, |> |> I don't know if you were watching about 1-2 weeks ago, as this forum went |> 'round and 'round about subtracting 6 months from a date. The problem |> dealt with end-of-month issues and 28/29/30/31 days per month. |> |> Since you specifically state that you are dealing with cron, I will make |> the simplifying assumption that you just want to get rid of stuff that is |> more than about 182 days old, which is easy. |> |> Further assuming that the `datestamp' field is a date (not a datetime): |> DELETE FROM userlog WHERE datestamp < TODAY - 182; |> |> The same thing can be done using datetime fields, but the syntax is: |> DELETE FROM userlog WHERE datetimestamp < CURRENT - 182 UNITS DAYS; |> |> Hope that helps. |> Regards, |> Alan ___________________________ |> ______________________| R. Alan Popiel |__________________________ |> \\ Internet: | Martin Marietta, SLS | / |> \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / |> )Voice: | Denver, CO 80201-0179 USA | ( |> / 303-977-9998 |___________________________| (But you knew that!) \\ |> /________________________) (____________________________\\ |> -- Kenton Milligan kmill@rchland.vnet.ibm.com GO VIKES!!