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:

        SELECT *
        FROM tblMyTableName
          Where DateReceived BETWEEN 40358 AND 40722
        ORDER BY ID

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>