• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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>
0
dcorbran
Asked:
dcorbran
  • 7
  • 3
1 Solution
 
HamdyHassanCommented:

Try

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



Is DateDiff is database function? Is it working when you run sql from database ?
0
 
HamdyHassanCommented:
forget to mention the reason
you add extra "#" around database field
pressreleases.expiredate

that confuse coldfusion and think it's an expression
0
 
dcorbranAuthor Commented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HamdyHassanCommented:
"Diffdate is a coldfusion expression " 

You need to use database functions for that, which database you are running sybase/oracle...etc?

ColdFusion is evaluate all expression that based on coldfusion variables, prepare a final SQL and send it to database engine for processing, so if you want to compare dates on row level, you need to use database functions.


for sybase, so you can use
AND pressreleases.expiredate > getdate()
0
 
dcorbranAuthor Commented:
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>
0
 
HamdyHassanCommented:
very good
0
 
HamdyHassanCommented:
It seems you are a new user to EE
Please reward your points if your think the problem is solved.
Good Luck
0
 
HamdyHassanCommented:
Hello
0
 
dcorbranAuthor Commented:
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.
0
 
HamdyHassanCommented:
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now