date add in query issue 1

[alsjka]

I want to be able to return all records that are greater than 4 weeks from the date received. I want to use the following query is this possible?


select ponum, count(itemnum) as itemnum, count(invoicenum) as invoicenum
from poreceiv
where ponum = '#get_po_item.ponum#'
and itemnum = '#get_po_item.item#'
and datereceived > #dateadd("ww", -4, datereceived)#

 

[TamedTech]

select ponum, count(itemnum) as itemnum, count(invoicenum) as invoicenum
from poreceiv
where ponum = '#get_po_item.ponum#'
and itemnum = '#get_po_item.item#'
and datereceived LT #(NOW()-28)#

I'm away from my environment at the moment but i think that will return all results that have not be recieved in the last 28 days, so are more than 28 days old.

I'm probably wrong, but thought i'd give it a shot.

Rob

 

[joepeacock]

Just add createodbcdate() to your code and it's perfect:

select ponum, count(itemnum) as itemnum, count(invoicenum) as invoicenum
from poreceiv
where ponum = '#get_po_item.ponum#'
and itemnum = '#get_po_item.item#'
and datereceived > #[b][COLOR=red]createodbcdate([/color][/b]dateadd("ww", -4, datereceived)[b][COLOR=red])[/color][/b]#

 

[joepeacock]

My code above assumes you have a local variable called #datereceived# that you are comparing the database field against. I think what you actually want is this:

select ponum, count(itemnum) as itemnum, count(invoicenum) as invoicenum
from poreceiv
where ponum = '#get_po_item.ponum#'
and itemnum = '#get_po_item.item#'
and datereceived < #createodbcdate(dateadd("ww", -4, now()))#

 

[imstillatwork]

just use cfqueryparam, it will format your dates correctly for your database. You should use cfqueryparam on all of your CF variables in your query.

select ponum, count(itemnum) as itemnum, count(invoicenum) as invoicenum
from poreceiv
where ponum = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#get_po_item.ponum#">
and itemnum = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#get_po_item.item#">
and datereceived < <cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateadd("ww", -4, now())#">

Kevin

http://www.lot-o-nothin.com/cfml/

 

[alsjka]

Thanks all great reply's!

I will try this when at work tomorrow.

JKA!