|
|
| Author |
Message |
babu_hi
Active User
Joined: 11 Apr 2006
Posts: 66
|
|
|
|
I want to retrieve the Data form Current Month to Past 24 months data form the Db2 table.
I have a control table and it is controlling the 85 tables and every time only one table is in Active Satae(A) and remaining are in Hestory Status(H) for the status flag column in CONTROL TABLE, Now i want to implement a interface must be able to return one or many table names to it based upon what the calling program request. For example I was to starting search the 85 tables starting with the current month for the last 24 month of history in this case you would return the last 24 table name who date match my search. |
|
| Back to top |
|
 |
References
|
 Posted: Tue Apr 29, 2008 2:51 pm Post subject: Re: Retrieve the Data form Current Month to Past 24 months |
|
|
|
|
enrico-sorichetti
Global Moderator
Joined: 14 Mar 2007
Posts: 2562
Location: italy
|
|
|
|
posting twice will not get You a faster answer..
I will delete the other topic  |
|
| Back to top |
|
|
ashimer
Senior Member
Joined: 13 Feb 2004
Posts: 313
Location: Bangalore
|
|
|
|
try this ....
| Code: |
SELECT * FROM table WHERE
DAY(EMPDATE) between 1 and DAY(CURRENT DATE) AND
MONTH(EMPDATE) between 1 and MONTH(CURRENT DATE) AND
YEAR(EMPDATE) BETWEEN YEAR(CURRENT DATE)-2 AND YEAR(CURRENT DATE);
where EMPDATE will be the date field in your table ..
try and let us know ..
|
|
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 19
Location: pune
|
|
|
|
Try this...
SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS); |
|
| Back to top |
|
|
shrivatsa
Active User
Joined: 17 Mar 2006
Posts: 157
Location: Pune
|
|
|
|
| nabarundas wrote: |
Try this...
SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS); |
insted > I think he needs last of Last two years
So he has to use < comparision |
|
| Back to top |
|
|
babu_hi
Active User
Joined: 11 Apr 2006
Posts: 66
|
|
|
|
while executing this query i am getting sqlcode=180 SELECT * FROM tablename
WHERE date_column > (CURRENT_DATE - 2 YEARS);
here my date column contain only year and month with type "200803".
error info :
DSNT408I SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE CNTL_DTE IS
INVALID
DSNT418I SQLSTATE = 22007 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODTV SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 19
Location: pune
|
|
|
|
Hi babu_hi,
Can you tell me the datatype of the date column. If it is char then we have to go for different procedure. |
|
| Back to top |
|
|
ashimer
Senior Member
Joined: 13 Feb 2004
Posts: 313
Location: Bangalore
|
|
|
|
200803 is not a valid date format
here ull have to use
SELECT * FROM tablename
WHERE substr(date_column,1,4) > year(CURRENT_DATE - 2 YEARS) and substr(date_column,5,2) >= month(current date); |
|
| Back to top |
|
|
|
|
