CODEWITHSUNDEEP
sqlcoldfusion
user1812107
user1812107

Reputation: 13

ColdFusion query four days ago

I want to query order information from 4 days ago. I cannot quite get the query to use the full 24hours of the day though:

<cfquery name="rsByDate" datasource="#request.dsn#">
    SELECT tbl_customers.cst_FirstName
            , tbl_customers.cst_LastName
            , tbl_customers.cst_Email
            , tbl_orders.order_ID
            , tbl_orders.order_Date
    FROM  tbl_customers INNER JOIN tbl_orders 
               ON tbl_customers.cst_ID = tbl_orders.order_CustomerID
    WHERE tbl_orders.order_Date >= #CreateODBCDateTime(DateAdd("d",-5,Now()))#
    AND   tbl_orders.order_Date <= #CreateODBCDateTime(DateAdd("d",-4,Now()))#
    AND   order_Status = 3
    ORDER BY tbl_orders.order_Date DESC
</cfquery>

What I'm hoping to achieve is to perform this query and then use cfmail to ask the customers to review us four days after their order is marked as status 3 'shipped'.

I think I have the date settings not quite right. Where am I going wrong?

Upvotes: 1

Views: 123

Answers (1)

Adam Cameron
Adam Cameron

Reputation: 29870

It's because you're basing your filter dates on now(), which has a time component. So now()-4 for me is 5/11/2012, but at 12:10pm. You only want to use the date part for the filter.

You should never hard-code your dynamic values in your SQL string anyhow, so firstly use <cfqueryparam> to parameterise your dynamic values, and then use a CF_SQL_DATE as the type, which should only pass the date through, not the date/time. I am not in a position to test this for you at the moment, but try that... if it still passes the time part of the date/time, then create a date object with just the date part, using createDate().

Upvotes: 3

Related Questions