Unanswered: Problem's Getting Date Function Working?
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
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?
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?
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.