Date Comparision in CFQUERY in Coldfusion

Posted on 2011-10-24
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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    My coldfusion is rusty

    What have you tried?

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

    LVL 51

    Accepted Solution

    >  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'}  --->
    WHERE  WHDeliveryDate IS NULL
    OR         WHDeliveryDate >= <cfqueryparam value="#dateAdd('d', -14, now())#" cfsqltype="cf_sql_date">

    Author Comment

    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 51

    Expert Comment

    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.  


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
    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 …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now