Databases
StackOverflow2013
Postsdbo
POUsing BETWEEN with Dates SQL and Coldfusion
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing BETWEEN with Dates SQL and Coldfusion
    text
    copied!<p>I am trying to grab orders between certain dates using the <code>BETWEEN</code> operator in SQL. </p> <p>My date formats are in dd/mm/yyyy format. </p> <p>ord_fdate is the from date: Currently 01/03/2012<br> ord_tdate is the to date: Currently 07/03/2012</p> <p>I thought this would return all orders from 01/03/2012 (including 01/03/2012) to 07/03/2012 (including 07/03/2012). </p> <p>However, it does not get orders with dates 07/03/2012</p> <p>My query below; i have also included the debug output of the query;</p> <pre><code>&lt;cfset ord_fdate = DateFormat(ord_fdate, "dd/mm/yyyy")&gt; &lt;cfset ord_tdate = DateFormat(ord_tdate, "dd/mm/yyyy")&gt; &lt;cfquery name="getOrders" datasource="#application.dsn#"&gt; SELECT dbo.tbl_orders.uid_orders, dbo.tbl_orders.dte_order_stamp FROM dbo.tbl_orders WHERE dbo.tbl_orders.uid_order_webid=&lt;cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#"&gt; AND bit_order_archive=&lt;cfqueryparam cfsqltype="cf_sql_bit" value="no"&gt; AND txt_order_status=&lt;cfqueryparam cfsqltype="cf_sql_varchar" value="Awaiting Dispatch"&gt; AND dte_order_stamp BETWEEN &lt;cfqueryparam cfsqltype="cf_sql_date" value="#createODBCDate(ord_fdate)#"&gt; AND &lt;cfqueryparam cfsqltype="cf_sql_date" value="#createODBCDate(ord_tdate)#"&gt; ORDER BY dte_order_stamp DESC &lt;/cfquery&gt; </code></pre> <p>Debug;</p> <pre><code>WHERE (dbo.tbl_orders.uid_order_webid=? AND bit_order_archive=? AND txt_order_status=? AND dte_order_stamp BETWEEN ? AND ?) ORDER BY dte_order_stamp DESC Query Parameter Value(s) - Parameter #1(cf_sql_integer) = 1 Parameter #2(cf_sql_bit) = NO Parameter #3(cf_sql_varchar) = Awaiting Dispatch Parameter #4(cf_sql_date) = {ts '2012-03-01 00:00:00'} Parameter #5(cf_sql_date) = {ts '2012-03-07 00:00:00'} </code></pre> <p>Not sure why it is not working. </p> <p>Database is SQL 2008</p> <p>Any ideas?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload