Date Comparision in CFQUERY in Coldfusion


  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.
Who is Participating?
_agx_Connect With a Mentor Commented:
>  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">
slightwv (䄆 Netminder) Commented:
My coldfusion is rusty

What have you tried?

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

sgleeAuthor Commented:
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.
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.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.