help with adding calculated aged days to the sql command section

[memewang]

I have created the sql statement under the command section of the database expert. I want to added a calculated filed to calculate # of aged days and make that as a table field. How would I do this?? I have tried by adding "today()-dbo.trans.trans_date as aged" but I don't think it is working. Can somebody please help??


select dbo.trans.item_id, dbo.trans.trans_date, dbo.customer.customer_name from dbo.trans left join dbo.customer on dbo.trans.trans_id=dbo.customer.trans_id

 

[lynchg]

Try using:

DateDiff("d",dbo.trans.trans_date,CurrentDate) as Aged

 

[memewang]

I got an error message" failed to open a rowset: details: 42S22:[oracle][odbc]"datediff": invalid identifier" I want to add to the command section of the database expert as a select statement. Can somebody please help?



select
DateDiff("d","dbo.trans"."trans_date",CurrentDate) as Aged, "dbo.trans"."item_id", "dbo.trans"."trans_date"
from dbo.trans left join dbo.customer on dbo.trans.trans_id=dbo.customer.trans_id

 

[lbass]

When you say that the following isn't working:

today()-dbo.trans.trans_date as aged

...what exactly is happening? Do you get an error message when you try to complete the command? If so, what is it?

When I use Oracle, I would set it up like this:

Select {fn now()}-"table"."date" "Aged"

Not sure this will work for you, but it might be worth a try.

-LB