|
View previous topic :: View next topic
|
| Author |
Message |
shakti
New User
Joined: 20 Jan 2004
Posts: 42
|
|
|
|
My job is scheduled on last date of every month and this job would retrieve the current month's data from the DB2 table. Could you plesse let me know how retrieval is possible.
Sakthi |
|
| Back to top |
|
 |
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
what is the structure of the DB2 table?
you can retrieve the data easily if the table is containing any date or timestamp field. |
|
| Back to top |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex
|
|
|
|
Hi,
If you have month entity in your table you can do it as below.
| Code: |
SELECT var1, var2, var3, var4,month
FROM TABLE
WHERE month = 'march'
GROUP BY var1,var2,month,var3,var4; |
|
|
| Back to top |
|
|
shakti
New User
Joined: 20 Jan 2004
Posts: 42
|
|
|
|
Hi Gautam,
Thanks for the clarification.
Regarding your query, I have a table with timestamp as one of the column.
Could you please let me know how the retrival query could be?
Thanks in advance.
Shakti |
|
| Back to top |
|
|
vasanthkumarhb
Active User
Joined: 06 Sep 2007
Posts: 275
Location: Bang,iflex
|
|
|
|
Hi,
the variable contain the TIMESTAMP value which constitute date as well as time up to microseconds.
you take the substring of the timestamp to compare in the query to get the complete last month data.
check this query and try.
| Code: |
| SELECT * FROM my_table WHERE SUBSTRING(timestamp_val,1,8) = DATE '2002-10-03'; |
|
|
| Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
If I understand your requirement currectly, Will not the below solution work?
WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE)
Thanks,
Prajesh |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 165
Location: Bangalore
|
|
|
|
| Code: |
| SELECT * FROM MY_TABLE WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE) |
|
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Prajesh_v_p wrote: |
If I understand your requirement currectly, Will not the below solution work?
WHERE MONTH(timestamp_val) = MONTH(CURRENT DATE)
Thanks,
Prajesh |
It will work Prajesh.  |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
What if there is more than 1 years worth of data in the table?
| Code: |
WHERE DATE(TIMESTAMP_VAL) BETWEEN (CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS)
AND CURRENT DATE |
What will you do if the query can't be executed on the last day of the month?
What will you do if there is additional data added to the table after the query is executed and before the end of the day? |
|
| Back to top |
|
|
|
|
