2 Replies Latest reply on Jun 12, 2009 9:54 AM by -==cfSearching==-

    cfquery - selecting records where DateDiff >=0


      I am trying to query a database and only show the records that are not set to expire. When I run a DateDiff on the table I pull up all records, although one of them is a -1 when you do the comparison.  Here's a rought breakdown of the query.


      <!--- create variable with current date --->

      <cfset DaNow=#Dateformat(now(),'mm/dd/yyyy')#>


      <!-- grab records that have yet to expire --->

      <cfquery name="test" datasource="TestDB">

      SELECT * FROM adVerts

      WHERE (DateDiff('d',#DaNow#,EndDate) >=0) AND (AdType = 'BigBox')



      I have three records that come back as  -1, 220, 150.


      I can use the CFIF tag to eliminate the -1 by doing a <CFIF  #DateDiff('d,DaNow,EndDate)# LT 0> tag but would like to know how to filter out the expired records via CFQUERY.


      Any help would be GREATLY appreciated!

        • 1. Re: cfquery - selecting records where DateDiff >=0
          mack_ Level 3

          One thing to note is that DateDiff from your cfquery is a database

          function and DateDiff from your cfif is a ColdFusion function and they

          might be returning different information. Try cfdumping this query and

          see what the result is:


          SELECT *, DateDiff('d',#DaNow#,EndDate) AS databaseDateDiffDiference
          FROM adVerts
          WHERE (DateDiff('d',#DaNow#,EndDate) >=0) AND (AdType = 'BigBox')



          • 2. Re: cfquery - selecting records where DateDiff >=0
            -==cfSearching==- Level 4

            CalTek wrote:


            WHERE (DateDiff('d',#DaNow#,EndDate) >=0) AND (AdType = 'BigBox')



            I have three records that come back as  -1, 220, 150.



            It may be that your date variable is not enclosed in quotes.  So your database is not treating it as a date string (ie "06/12/2009"), but is instead interpreting it as a numeric representation of date:  "06/12/2009" ->  06 divided by 12 divided by 2009 .  That produces completely a different date.


            But I would suggest using cfqueryparam to pass in a proper date object, rather than a string.  I would also restructure the query to get rid of the DateDiff statement. A simple comparison operator should do the job, and should yield better performance than DateDiff.  Plus it is more intuitive IMO. Though techically, either option would work.


            <!--- something along these lines --->

            WHERE   AdType = 'BigBox'

            AND       EndDate >=  <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">