Featured Post

Musing on the Interaxon Muse Meditation Headband

"For this calibration, find a comfortable position and take a deep breath". The computer brain interface world is getting int...

Wednesday, May 29, 2013

Coldfusion progamming FLOAT IsNumerc date comparison SQL

One of the systems I developed was done in the Coldfusion programming language. As I have developed and learned more about Coldfusion over the past 9 years or so, the more I realize it depends a lot on SQL. I learned how to do a lot of Coldfusion from another learner in the beginning, but after that, internet searching for code solutions is mostly what I do, as I imagine many programmers and code seekers do as well for answers. Since Coldfusion runs on a proprietary software platform and is not open source like PHP or MySQL, it is more difficult to find Coldfusion programmers to share ideas.

Recently I found the answer for a puzzle that had long baffled me, how to compare two dates then output data fields on a query for them. I knew how to do the SQL to output data from a month, day or year in the past, something like this:

<CFQUERY NAME="GetRecords" datasource="myAccessDB" >
      SELECT COUNT(id)as number_total FROM tblMyTableName Where  Month(DateReceived) Like '1' and DateReceived Like '%2010%' </cfquery>
      <cfoutput query="GetRecords">#number_total#</cfoutput><br>

In order to compare two dates in one year, I found a Coldfusion developer site that suggested using FLOAT, and here is how this is done:

   <CFQUERY NAME="GetMREBRecords" DATASOURCE="myAccessDB">
        SELECT *
        FROM tblMyTableName
          Where DateReceived BETWEEN 40358 AND 40722
        ORDER BY ID
        DESC
        </CFQUERY>

The FLOAT values in this example are actually dates. 40358 is June 30, 2010 and 40722 is July 1, 2011 (maybe give or take a day?). I have yet to find an actual table with FLOAT date equivalents. I am not even sure if this means "Floating point", a term or process used in computational data computing. All I know is, that it works for my system really well, and solved a big problem we had.

Actually, these FLOAT values have no known standard dates(times) that I can find, and are not accurate for leap years and other variables.  Thankfully, I kept searching the internet for some code that would work and finally found this:

<CFSET start_date = #CREATEODBCDATETIME("7/1/2012")#>
 <CFSET end_date = #CREATEODBCDATETIME("6/30/2013")#>

        <CFQUERY NAME="GetRecords" datasource="myAccessDB" >
      SELECT COUNT(id)as number_outstanding FROM MyTable 
   Where DateOfReceipt  Between #start_date# And #end_date# </cfquery>
          Number of 2012-2013 Records 
          = <cfoutput query="GetRecords">#number_outstanding#</cfoutput>