Databases
StackOverflow2013
Postsdbo
PODrilling down date span with ColdFusion or SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODrilling down date span with ColdFusion or SQL
    text
    copied!<p>I have a voucher system in my shopping cart, the vouchers have a valid from date and a valid to date. <strong>What is the best way to ensure they are valid?</strong></p> <p>I was stupidly setting today's date, and then asking the database if today's date is greater than or equal to the start date. i.e is 02/02/2012 <code>GTE</code> 15/02/2012 it's obviously not! I was then asking the database if the expiry date is <code>LTE</code> to today's date. i.e is 29/02/2012 <code>LTE</code> 15/02/2012 again it's not! Sounded right when I put it down paper!</p> <p>Here is the code below, i am using a custom tag to convert the date correctly as this helps greatly with European dates! FYI: The date fields are set to date in the sql database.</p> <p>What I need to do is make sure that today's date falls between the start date and end date. Would it be best to create a date span with ColdFusion or use the <code>BETWEEN</code> operator in ColdFusion??</p> <pre><code>&lt;cfset todaysDate = DateFormat(now(), "dd/mm/yyyy")&gt; &lt;CF_convertDate inputVariable="todaysDate" inputMask="EU" outputType="odbcdate" outputVariable="myDate"&gt; &lt;cfquery name="chk_voucher" datasource="#application.dsn#"&gt; SELECT uid_vouchers, txt_vouch_name, txt_vouch_descrip, txt_vouch_code, txt_vouch_type, txt_vouch_percent, txt_vouch_value, dte_vouch_expiry, dte_vouch_start, uid_vouch_webid, bit_vouch_archived, txt_vouch_asign FROM dbo.tbl_vouchers WHERE bit_vouch_archive=&lt;cfqueryparam cfsqltype="cf_sql_bit" value="no"&gt; AND dte_vouch_start &gt;= #myDate# AND dte_vouch_expiry &lt;= #myDate# AND txt_vouch_code=&lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#form.txt_vouch_code#"&gt; &lt;/cfquery&gt; </code></pre> <p>Any help would be appreciated!!</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