Skip to main content
C
CalTek
Participant
June 11, 2009
Question

cfquery - selecting records where DateDiff >=0

  • June 11, 2009
  • 2 replies
  • 1271 views

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')

</cfquery>

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!

This topic has been closed for replies.

2 replies

-
-__cfSearching__-
Inspiring
June 12, 2009

CalTek wrote:

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

</cfquery>

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">

M
mack_
Participating Frequently
June 12, 2009

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:

=0) AND (AdType = 'BigBox')
]]>

Mack

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices