asked on

How to exclude data with a date field past current date

I'm trying to set up a cfquery that will exclude rows in a database field in which the date in the expiredate field has passed. I've tried using DiffDate to find a negative result that would mean a passed date as below. Everything else in the example has been tested and works, but with the Diffdate line added I get an error that "expiredate is undefined in pressreleases" (I've triple-checked that expiredate is a legitimate field in that table.)

If anyone can spot an error or offer and alternative way to accomplish this I'd much appreciate it.

<cfif Form.startdate GT "">
<cfset VarStartDate = "#CreateODBCDate(form.StartDate)#">
<cfelse>
<cfset VarStartDate = "01/01/2003">
</cfif>
<cfif Form.EndDate GT "">
<cfset VarEndDate = "#CreateODBCDate(form.EndDate)#">
<cfelse>
<cfset VarEndDate = "#DateFormat(Now(), 'mm/dd/yyyy')#">
</cfif>

<cfquery name="releaseSearch" datasource="pressreleases">
SELECT autoid, releasedate, expiredate, subject, title FROM pressreleases

WHERE (pressreleases.releasedate BETWEEN #VarStartDate# AND #VarEndDate#)
AND (subject LIKE '%#form.subjectid#%')
AND (DateDiff(#pressreleases.expiredate#, #DateFormat(Now(), 'mm/dd/yyyy')# < 0))

ORDER BY releasedate DESC

</cfquery>

HamdyHassan

Try

AND ( DateDiff(expiredate, #DateFormat(Now(), 'mm/dd/yyyy')# < 0))

Is DateDiff is database function? Is it working when you run sql from database ?

HamdyHassan

forget to mention the reason
you add extra "#" around database field
pressreleases.expiredate

that confuse coldfusion and think it's an expression

dcorbran

ASKER

Unfortunately without the #'s expiredate goes through as a string instead of reading the value of the field for the expression. I believe I read that the # is necessary to make CF read a value.

Diffdate is a coldfusion expression to compare two dates and calculate the number of days (weeks, hours, months, etc.) between the two parameters. I'm also experimenting with CompareDate (delivers values of +1, 0 or -1 depending on which parameter is later), but being new to CF and DB's not sure if they're supposed to work within a query.

ASKER CERTIFIED SOLUTION

HamdyHassan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dcorbran

ASKER

This solved it--thanks for helping:
Had to create ODBC date format for the query to be able to understand it.

<cfif Form.startdate GT "">
<cfset VarStartDate = "#CreateODBCDate(form.StartDate)#">
<cfelse>
<cfset VarStartDate = "01/01/2003">
</cfif>
<cfif Form.EndDate GT "">
<cfset VarEndDate = "#CreateODBCDate(form.EndDate)#">
<cfelse>
<cfset VarEndDate = "#DateFormat(Now(), 'mm/dd/yyyy')#">
</cfif>
<cfset Now = "#DateFormat(Now(), 'mm/dd/yyyy')#">

<cfquery name="releaseSearch" datasource="pressreleases">
SELECT autoid, releasedate, expiredate, subject, title FROM pressreleases

WHERE (pressreleases.releasedate BETWEEN #VarStartDate# AND #VarEndDate#)
AND (subject LIKE '%#form.subjectid#%')
AND (pressreleases.expiredate > #CreateODBCDate(Now)#)

ORDER BY releasedate DESC

</cfquery>

HamdyHassan

very good

HamdyHassan

It seems you are a new user to EE
Please reward your points if your think the problem is solved.
Good Luck

HamdyHassan

Hello

dcorbran

ASKER

While your proposed solution didn't actually work, it led me on the right path of separating the dbase functions from the CF functions.

Sorry the previous accept I tried didn't go through for some reason.

HamdyHassan

Finding a solution by your own doesn't mean you gave who try to help you a grade "C".

I understand you give "C" or even "B" to somebody give you very poor solution that works but you know there is better way to do this solution.

In my case, you said my solution is not working for you !!!

In my case, I did help you to separate dbase from CF functions, plus I give you the final solution
AND pressreleases.expiredate > getdate()
that works in case you have Sybase, but it seems you have another DBMS, that's why it's not working

Thanks