4 Replies Latest reply: Feb 16, 2007 6:00 AM by Dan Bracuk RSS

    cfqueryparam appears to change answer

    Dan Bracuk Community Member
    Dan Bracuk Feb 15, 2007 2:51 PM
      Database is oracle 9i
      <cfscript>
      datestart = createdate(2007,02,14);
      dateend = createdatetime(2006,2,14,23,59,59);
      </cfscript>
      <cfdump var="start is #datestart# end is #dateend#">

      This shows
      start is {ts '2007-02-14 00:00:00'} end is {ts '2006-02-14 23:59:59'}
      which is what you expect.

      Here is the de-bugging info for hard coding those values. Returns 130 records
      x (Datasource=sisi, Time=16ms, Records=130)
      select procedure_date_dt
      from patient_care_event
      where procedure_date_dt >= {ts '2007-02-14 00:00:00'}
      and procedure_date_dt < {ts '2007-02-14 23:59:59'}
      and cancelled_tf = 0

      and here is it with cfqueryparam. 0 records returned
      y (Datasource=sisi, Time=0ms, Records=0)
      select procedure_date_dt
      from patient_care_event pce
      where procedure_date_dt >= ?
      and procedure_date_dt <?
      and cancelled_tf = 0

      Query Parameter Value(s) -
      Parameter #1(cf_sql_date) = {ts '2007-02-14 00:00:00'}
      Parameter #2(cf_sql_date) = {ts '2006-02-14 23:59:59'}

      Here is the applicable code from the second query

      where procedure_date_dt >= <cfqueryparam cfsqltype="cf_sql_date" value="#DateStart#">
      and procedure_date_dt < <cfqueryparam cfsqltype="cf_sql_date" value="#DateEnd#">

      I changed the cfsqltype to cf_sql_timestamp but the results didn't change.

      Any theories.
      • 665 Views
      • Tags:
        • 1. Re: cfqueryparam appears to change answer
          Newsgroup_User Community Member
          Newsgroup_User Feb 15, 2007 3:11 PM (in response to Dan Bracuk)
          I've come across weirdness like this too. It SEEMS like Oracle doesn't
          like the ODBC-style date string (although not enough to actually error).

          You need to use the TO_DATE function (or one of its ilk):
          http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/functions137a.htm#SQLRF0 6132.

          I believe you can still use <cfqueryparam> in the CHAR component of the
          expression.

          (I'd test this out for you, but I don't have easy access to our Oracle
          server at present, sorry).

          --
          Adam
          • 2. Re: cfqueryparam appears to change answer
            Charlie Griefer Community Member
            Charlie Griefer Feb 15, 2007 3:30 PM (in response to Dan Bracuk)
            in your <cfscript> block, you're setting the dateend variable to 2006.

            Parameter #2(cf_sql_date) = {ts '2006-02-14 23:59:59'}
            • 3. Re: cfqueryparam appears to change answer
              Dan Bracuk Community Member
              Dan Bracuk Feb 15, 2007 8:40 PM (in response to Charlie Griefer)
              quote:
              Originally posted by: [CJ]
              in your <cfscript> block, you're setting the dateend variable to 2006.
              Parameter #2(cf_sql_date) = {ts '2006-02-14 23:59:59'}

              While true, that might not matter. The original problem came up using form fields. But I'll give it a look when I get back to work tomorrow.
              • 4. Re: cfqueryparam appears to change answer
                Dan Bracuk Community Member
                Dan Bracuk Feb 16, 2007 6:00 AM (in response to Dan Bracuk)
                I was screwing it up. The code I posted was not returning rows because I had the wrong year.

                For those that are curious, this was my original problem.

                Years ago I wrote a custom tag that accepted two form fields and:
                - ensured both were dates
                - ensured they were in the right order
                - ensured both were within a pre-defined range,
                and
                - ensured they were not too far apart.

                If they passed muster, the tag would return two "dates" in yyyy-mm-dd format. These are of course strings, but everything worked, so it didn't really matter.

                In this particular template, I ran this piece of code:
                DateEnd = DateEnd & " 23:59:59";

                and then of course this:
                and procedure_date_dt < <cfqueryparam cfsqltype="cf_sql_timestamp" value="#DateEnd#">

                That's where I was getting my unexpected results.

                Correcting the custom tag means re-writing every template that uses it, so that's not going to happen. The band-aid fix is to change this
                DateEnd = DateEnd & " 23:59:59";
                to this
                DateEnd = DateAdd("d", 1, DateEnd);

                Thank you to those who spent time trying to figure this out.