date compare ?

[daNewfie]

I have a field called "posted" in the sql DB set to date/time

I want to write a query that will only return results of items posted within the last seven days

...
<cfquery...
...
where posted ?????
</cfquery>


Craig

 

[imstillatwork]

where posted > <cfqueryparam cfsqltype="CF_SQL_DATE" value="dateadd("d",-7,now())">

Could be syntacticly wrong, but is theoretically correct. I think.


Kevin

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

 

[FALCONSEYE]

or...

select * from myTable
where DateDiff(d, myPostDate, getdate()) <= 7

http://www.jetansolutions.com

 

[r937]

falcon, that'll only work in certain databases, and furthermore, because you're using a function on the date column, the index will be ignored and the query will do a table scan

kevin had the right idea, let coldfusion pass in a date constant, which will allow the database to use the index

r937.com | rudy.ca

 

[daNewfie]

This is what I got and tried numerous variations but cant get it to work

getting Invalid token "d"

SELECT memberId, username, password, firstName, lastName, email, dateReg
    FROM members
	where dateReg  <cfqueryparam value="#qGetMembers.dateReg#("d",-7,#now()#)" cfsqltype="CF_SQL_DATE" />

 

[r937]

the value for the CFQUERYPARAM should be the result of applying the coldfusion DateAdd function with a parameter of -7 days to the current date

... where dateReg >
<cfqueryparam
value="#DateAdd("d",-7,Now())#"
cfsqltype="CF_SQL_DATE" />

r937.com | rudy.ca