Hello,

Consider this as a oracle Query
[start]
select fg.PROJECT_ID id
from fgt_assignments fg
where to_char(fg.UPDATED_ON,'mm-dd-yyyy') >= to_char(CURRENT_DATE - 1,'mm-dd-yyyy')
-- and to_char(fg.UPDATED_ON,'mm-dd-yyyy') <= to_char(CURRENT_DATE,'mm-dd-yyyy')
union
select tp.PROJECT_ID id
from tpt_expenses tp
where to_char(tp.UPDATED_ON,'mm-dd-yyyy') >= to_char(CURRENT_DATE - 1,'mm-dd-yyyy')
-- and to_char(tp.UPDATED_ON,'mm-dd-yyyy') <= to_char(CURRENT_DATE,'mm-dd-yyyy')
[end]

can anyone of you convert this into db2 Query, I did try it using cast() method into char but didn't work. Is there any other way to go about this.

Thanks

You don't need to cast the dates, just use regular date arithmatic. See the SQL Reference Manual, Vol 1 for datails on how date arithmatic works.

Thanks you mean to say that i should do as follows
[Start]
select assign.PROJECT_ID id from fgt_assignments assign
where assign.UPDATED_ON >= current timestamp - 1
and assign.UPDATED_ON <= current timestamp
union
select expenses.PROJECT_ID id from tpt_expenses expenses
where expenses.UPDATED_ON >= current timestamp - 1
and expenses.UPDATED_ON <= current timestamp
[End]

The only missing piece is the unit on the "-1". You have to tell the system whether you want to substract 1 year, 1 month, or 1 day.

Not exactly. What I meant is that you should read the manual I mentioned (the manual also has examples to help you).

You can download the PDF manuals for free.

i HAVE GOT IT right with some % here is the Query

[start]
select exp.PROJECT_ID id, day(exp.UPDATED_ON) from expenses as exp
where date(exp.UPDATED_ON) >= current date
[end]
In the Table UPDATED_ON is of type Timestamp, but my problem is How to get one day back from the date as UPDATED_ON
[start]
select exp.PROJECT_ID id from expenses AS exp
where DATE(exp.UPDATED_ON) <= DATE(current Date) - DAY(current Date) - 1
[end]
The Current date -1 does not work is there any other way to get the date one day back.

and can you please post the links for the PDF Manuals from where i can download it

Thanks.

Here is version 9.5 manuals
IBM - DB2 Version 9.5 for Linux, UNIX and Windows English manuals

Here is 9.1
IBM - DB2 9 for Linux, UNIX and Windows manuals

Here is 8.2
IBM - DB2 UDB Version 8 Product Manuals

where exp.UPDATED_ON <= current date - 1 day

Most of the things you are asking are quite basics.
For new to DB2 UDB this is a good starting point. Free cookbook

http://mysite.verizon.net/Graeme_Bir...k/DB2V95CK.PDF