Hello!

We have a process in place which tracks certain "hits" through an ODBC connection - right now, the entire code (which returns what i want) looks like this:

SELECT A.incdnt_rec_typ AS CATEGORY, COUNT (DISTINCT A.INCDNT_ID) AS QTY, B.DIV_NM_EN AS DIVISION_NAME, c.sts_desc_en AS STATUS

FROM PQ5.INCDNT_ALL A, PQ5.DIV B, pq5.sts_all c
WHERE
A.DIV_ID = B.DIV_ID
AND a.sts_cd = c.sts_cd
AND A.incdnt_rec_typ = 'CAR'
AND date(creat_tmstmp) < '2009-07-01'

GROUP BY A.incdnt_rec_typ, B.DIV_NM_EN, c.sts_desc_en;

I want to modify the the code so, simply, instead of having to adjust the "month" every different time i run it (on a monthly basis - i.e.// in august, i'd have to change it to "2009-08-01", it only includes things from the last day of each month, and before - makes sense?

Any help is appreciated.
Andrew.

Turn it into this:

and creat_tmstmp < timestamp(current date - (day(current date) - 1) days,'00:00:00')

Andy

Works like a charm!

Just to make sure i understand - you are tellign it to look at anything created before the "date" of today's date - today's date (which would bring you to the first of this month), -1 (which woudl bring you to the last day of the month before.

CorrecT? Would this inclue things created on the last day of the previous month? I.E.// jun 30/09?
Andrew.

That is incorrect. the DAY function returns the day of the month of the expression. So, for today (7/31/2009), the DAY function return 31. By subtracting 1 from that we get 30. By subtracting 30 days from today's date we get the first of the month. I set the time portion of the timestamp to midnight, so that the resulting timestamp used for comparison is, for today's date would be 2009-07-01-00:00:00.000000. So your comparison would get what you want.

Andy

While this had nothing to do with your specific query, here is another date trick I've found useful:

select (current date - x.offset days) as saturday_of_this_week
from table(values(dayofweek(current date) - 7)) as x(offset);