?
Solved

How to exclude data with a date field past current date

Posted on 2003-02-25
10
Medium Priority
?
163 Views
Last Modified: 2013-12-24
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
Comment
Question by:dcorbran
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8020156

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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8020179
forget to mention the reason
you add extra "#" around database field
pressreleases.expiredate

that confuse coldfusion and think it's an expression
0
 

Author Comment

by:dcorbran
ID: 8020253
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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 9

Accepted Solution

by:
HamdyHassan earned 100 total points
ID: 8020393
"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
 

Author Comment

by:dcorbran
ID: 8020709
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8022013
very good
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8024792
It seems you are a new user to EE
Please reward your points if your think the problem is solved.
Good Luck
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8071559
Hello
0
 

Author Comment

by:dcorbran
ID: 8072538
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8073067
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question