View previous topic :: View next topic
|
Author |
Message |
venkatarao
New User
Joined: 19 Dec 2004 Posts: 23 Location: hyderabad
|
|
|
|
Hi,
Can any one tell me the select statement in DB2,to retrieve the current day transactions from one table.
My table have one field Transaction_Date, which contains Timestamp value.
so i want select the current date transactions daily from that table using that Transaction_date field.
This job i want to run daily,because of that i am not hardcoding the date in where clause.
Thanks,
venkat |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
If the job is to run daily, and extract transactions for yesterday or today, do a SET :WS-TIMESTAMP = current_timestamp - 1 days (if for yesterday). or without the - 1 days if only for today. be careful if you are trying to extract fridays work on monday.
anyway, move ws-timestamp after the SET to ws-ts-upper-range and ws-ts-lower-range. modifiy the hours, min, sec, millisecs in lower to zeros and the upper range to 23 59 59 99999. use the upper and lower ranges in a where clause column_ts between upper and lower.
you might think about having a place to store the last timestamp used for extraction. then manipulate that with sql + 1 days for your current daily extraction needs. That would enable you to extract on fri, save the low-range timestamp (because it is already set to zeroes). use sql to add 1 day and generate your high range based on today. that way you can extract saturday, sun and mon on monday. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
venkatarao wrote: |
Hi,
Can any one tell me the select statement in DB2,to retrieve the current day transactions from one table.
My table have one field Transaction_Date, which contains Timestamp value.
so i want select the current date transactions daily from that table using that Transaction_date field.
This job i want to run daily,because of that i am not hardcoding the date in where clause.
Thanks,
venkat |
Code: |
SELECT * FROM TABLE WHERE DATE(TRANSACTION_DATE) = CURRENT DATE
|
Why does a column called TRANSACTION_DATE contain a timestamp? |
|
Back to top |
|
|
|