| Author |
Topic |
|
andmunn
Starting Member
4 Posts |
 Posted - 2009-07-30 : 15:45:04
|
| Hi All,I'm having a problem - i need to write a qeury to return everything before the first of this month, using the create timestamp attribtue: i.e.// and date(creat_tmstmp) <= '2009-07-01'The above returns all the results previous to this month, for all years. I tried re-writing the criteria to:AND YEAR(creat_tmstmp) <= YEAR(Current_date)AND MONTH(creat_tmstmp) < MONTH(Current_date)But it omits anything created in 2008 - how do i go about correcting this?I know i get everything created in 2008 by using:AND YEAR(creat_tmstmp) < YEAR(Current_date)So basically i need to combine the two - any advice?Andrew. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
andmunn
Starting Member
4 Posts |
Posted - 2009-07-31 : 09:34:20
|
| Hi Brett,We have a process in place which tracks certain "hits" through an ODBC connection - right now, the entire sql 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 STATUSFROM PQ5.INCDNT_ALL A, PQ5.DIV B, pq5.sts_all cWHERE 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? |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-31 : 09:38:19
|
[code]AND date(creat_tmstmp) < dateadd(month, datediff(month, 0, getdate()), 0)[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
andmunn
Starting Member
4 Posts |
Posted - 2009-07-31 : 09:46:13
|
| Thanks KH,I attempted to modify my code as such - i get an error "206" - "MONTH" is not valid in the context it is used....Tried playing around a little bit - not sure what's wrong. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-31 : 09:58:06
|
can you post the query you used and the complete error message ?Are you using SQL 2000 or 2005/2008 ?Where are you running this query ? Tried in the query window ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
andmunn
Starting Member
4 Posts |
Posted - 2009-07-31 : 10:17:56
|
| HI KH,I am running this query through Teradata, connecting to the database via an ODBC connection. The complete error message i get is:"206: SQL0206N "MONTH" is not valid in the context where it is used. SQLSTATE=42703Output directed to Answerset window"Regarding which SQL i'm using... really, i'm not sure... i don't maintain / run the database, i simple run ocasional queries off it for reporting purposes.The query i used is posted exactly in the previous post... not sure if you mean something else?Andrew. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-31 : 10:43:11
|
Can you check if you are using SQL Server ? Google for SQL0206N give me links to DB2.FYI, this is a Microsoft SQL Server forum. Do find out what database you are using and post in appropriate forum
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Code to return Items Created before This Month?