Date format and SQL Query clarification

Please consider the following piece of code ( In the context of my previous thread Playing around with date range in ColdFusion 8).

<cfparam name="Form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
<cfparam name="Form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
<cfparam name="Form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">


<cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">
<cfinput type="dateField" name="enddate" label="End Date" width="100" value="#Form.enddate#">
<cfinput name="submit" type="submit" value = "Apply">
<cfinput name="cancel" type="submit" value="Download CSV">

I have following questions:

1) When the user will select a date(startdate) from the calender, is the date automatically going to pass to the "value" field of the following line?

 <cfinput type="dateField" name="startdate" label="Start Date" width="100" value="#Form.startdate#">

If the above is true then I believe the same thing will apply with the end date as well.

2) Regarding the SQL Query:

Suppose I'm writing the following query(Please note that "UpdatedDate" is the name of the column from where I'll be pulling up the "startdate" and "enddate":

             <cfquery datasource = "XX.XX.X.XX" name="qMyDatabase">
            SELECT(SELECT count(*) FROM MyDatabase) AS TOTAL_CONNECTIONS,
            (SELECT count(*) FROM MyDatabase WHERE event_vc = "OPEN" AND UpdatedDate BETWEEN #Form.startdate# AND #Form.enddate#  ) AS OPEN_CONNECTIONS,
            (SELECT count(*)FROM MyDatabase WHERE event_vc = "BOUNCE"    AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS BOUNCE_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DEFERRED" AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DEFERRED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DELIVERED" AND UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DELIVERED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "DROPPED"  AND  UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS DROPPED_CONNECTIONS,
            (SELECT count(*) from MyDatabase where event_vc = "PROCESSED" AND UpdatedDate    BETWEEN #Form.startdate# AND #Form.enddate#) AS PROCESSED_CONNECTIONS,
(ROUND((SELECT OPEN_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "OPEN",
(ROUND((SELECT DEFERRED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DEFERRED",
(ROUND((SELECT DELIVERED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DELIVERED", (ROUND((SELECT DROPPED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "DROPPED", (ROUND((SELECT PROCESSED_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "PROCESSED";
 </cfquery>

Please let me know if the above query seems to be correct?

3) Regarding the Date Format.

As mentioned above, I'm using the mask

mm/dd/yyyy

above , while the column(UpdatedDate) in my database uses different format

(YYYY/MM/DD HH:MI:SS)

. Is it going to create any problems?

Solution

That is a lot of questions for one thread .. but I will try and address most of them. (The first question you can answer yourself, just by trying it ;-)

As far as the query, even if it ran without error - it can be improved. I am not going to rewrite the query for you, but here are the main issues

First, never use raw client values directly in SQL. Always use cfqueryparam to protect against sql injection. It has other benefits as well, but that one is critical in a web application.
Second, you are passing in date strings. Date strings are ambiguous and can be misinterpreted, depending on the format and tool doing the parsing. It is much better use date objects instead. One way to do that is by using cfqueryparam and one of the date types: cf_sql_date (date only) or cf_sql_timestamp (date and time).
Third, as I mentioned on your other thread, you really need to simplify your query! That many subqueries is already unwieldy .. adding date filters to each subquery makes it downright unmanageable. I would recommend looking at ways to simplify it. Ed's suggestion offered one possibility, by reducing it to a single JOIN and a few function calls.

the column (UpdatedDate) uses YYYY/MM/DD HH:MI:SS

Well actually that is just how your IDE displays it to humans. It is not really stored that way. Internally, dates are stored as big numbers. However, your query does need to account for the fact that your column stores a date and time.

Say you wanted to retrieve all records dated in June:

    form.startDate = "06/01/2013"
    form.endDate = "06/30/2013"

Conceptually, you would need a sql expression like this:

    WHERE column BETWEEN '06/01/2013 at midnight' AND '06/30/2013 11:59:59 PM'

However, constructing those date/time values is a bit klunky IMO. A simpler way to handle it is using this paradigm:

   WHERE column >= {startDateAtMidnight}        
   AND   column <  {dayAfterEndDateAtMidnight}

Your actual query filter would look something like this:

    WHERE column >= <cfqueryparam value="#form.startDate#" 
                                  cfsqltype="cf_sql_date">
    AND   column <  <cfqueryparam value="#dateAdd('d', 1, form.endDate)#" 
                                  cfsqltype="cf_sql_date">

By adding one day to form.endDate, and using a < comparison, the resulting query is:

    WHERE column >= '2013-06-01 00:00:00'  
    AND   column < '2013-07-01 00:00:00'

This will produce the exact same results as the earlier BETWEEN expression.