May 7, 2008 at 12:54 pm
Hi all,
I'm writing a script that is going to look for old invoices. I've got everything working the way I want it to except I don't know how to write the "look for invoices older than xxx days" part.
My goal:
Report all invoices that are $500 or more AND have are older than 90 days from the current date.
If anyone can give me a hand with the last part, it would be greatly appreciated. Here's what I have so far.....
select
p21_invoice_amt_remaining_view.invoice_no,
p21_invoice_amt_remaining_view.amt_remaining_frominv,
p21_invoice_amt_remaining_view.order_no,
p21_view_invoice_hdr.customer_id,
p21_customer_view.customer_name,
p21_view_invoice_hdr.invoice_date
from
p21_invoice_amt_remaining_view
left outer join p21_view_invoice_hdr on p21_invoice_amt_remaining_view.invoice_no = p21_view_invoice_hdr.invoice_no
left outer join p21_customer_view on p21_view_invoice_hdr.customer_id = p21_customer_view.customer_id
where
p21_invoice_amt_remaining_view.amt_remaining_frominv > '499.99'
AND p21_view_invoice_hdr.invoice_date ???????
May 7, 2008 at 1:01 pm
p21_view_invoice_hdr.invoice_date < DATEADD(dd,-90,GETDATE())
- Zahran -
May 7, 2008 at 1:08 pm
You can use dateadd, or, because of the way DateTime data is stored in SQL Server, you can simply subtract from the date.
(DateAdd is in the prior example.)
and p21_view_invoice_hdr.invoice_date < getdate() -90
Either one will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 1:28 pm
Thanks for the replies guys - I also found that
(select dateadd(d,-90,CURRENT_TIMESTAMP))
works as well
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply