[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Date Comparision in CFQUERY in Coldfusion

Posted on 2011-10-24
Medium Priority
Last Modified: 2013-12-24

  I have a WarhouseDeliveryDate Field (Date/Time Field type in ACCESS) in the table and
  This field remains empty until the item is delivivered to the warehouse and then a user enters the date into the field.
  I need to come up with the query that satisfies the following condition.

 I like to select orders where WarehouseDeliveryDate is either empty (which means the order has not been delivered to the WH yet) or if they have a date filled in, it has to be newer than (today's date - 14).
 I have tried a various combination of syntax, but everytime it generates either an error.
 So far the only query that was successful was --->     WHERE   ([WHDeliveryDate] IS NULL)
 But I need a condition that meets 2nd criteria.

Thank you in advance for  your help.
Question by:sglee
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37020217
My coldfusion is rusty

What have you tried?

You should be able to use createodbcdate and use it in your where clause:

LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 37020218
>  newer than (today's date - 14).

Just use dateAdd() to get the date 14 days ago and it to your existing WHERE clause.  You may need to tweak it slightly.

<!--- ie If today is 10/24 this would result in  WHDeliveryDate >= {d '2011-10-10'}  --->
OR         WHDeliveryDate >= <cfqueryparam value="#dateAdd('d', -14, now())#" cfsqltype="cf_sql_date">

Author Comment

ID: 37020428
That worked exactly the way it should. Thank you for the query and I appreciate it.
Where can I find more example of Date Related comparison statements in a query on the Internet?
I googled it but I have not seen any example like yours.
LVL 52

Expert Comment

ID: 37020508
Truthfully, I haven't seen many myself.  But date comparisons are pretty simple. Once you have a date object, it's just like comparing numbers ie  =, >=, <=, etc....  The hardest part is figuring out what date you need to use.  For that I'd consult the docs on CF date functions.  


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

873 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