CODEWITHSUNDEEP
sqldatecoldfusionbetween
Danomite
Danomite

Reputation: 379

Coldfusion Query of Query missing results from date BETWEEN clause

I have a query called AllPosts from a query of a query.

AllPosts has two results:

Row 1| ID: 2   PublishedDate: 2012-05-30 16:47:00.0
Row 2| ID: 3   PublishedDate: 2012-05-31 15:50:00.0

When I do a query of a query on AllPosts called FilterPosts using 

WHERE PublishedDate BETWEEN '2012-05-01 00:00:00' AND '2012-05-31 23:59:00'

(Note that the actual query is doing cfqueryparam cf_sql_date for these values)

I expect FilterPosts to have the same rows as AllPosts, instead I only get ID 2. If I change ID 2's published date to 2012-05-30 23:59:50.0, FilterPosts returns the correct results.

I have also tried 

WHERE PublishedDate >= '2012-05-01 00:00:00'   
AND   PublishedDate <= '2012-05-31 23:59:00'

There is something specifically odd with May 31st.

I'm pulling my hair out here! Thanks!

I tried scratch building a the original query, and oddly enough, same results!

<cfset test = queryNew("Name,PublishedDate", "VarChar, Time" )>
<cfset queryAddRow(test)>    
<cfset querySetCell(test,"PublishedDate","2012-05-30 16:47:00.0")>     
<cfset querySetCell(test,"Name","First Entry")>    
<cfset queryAddRow(test)>    
<cfset querySetCell(test,"PublishedDate","2012-05-31 15:47:00.0")>    
<cfset querySetCell(test,"Name","Second Entry")>    

<cfset StartDate = CreateDate(2012, 5, 1)>

<cfquery name="filter" dbtype="query">    
    SELECT Name, PublishedDate    
    FROM    test
WHERE    1=1
AND PublishedDate >= <cfqueryparam value="#StartDate#" cfsqltype="cf_sql_date">
AND PublishedDate <= <cfqueryparam value="#DateAdd( 'n', -1, DateAdd('m', 1, StartDate) )#" cfsqltype="cf_sql_date">
 </cfquery>

The results of Filter only has 'FirstEntry' rather than both like it should.

Upvotes: 1

Views: 1202

Answers (3)

Danomite
Danomite

Reputation: 379

Oddly enough it had to do with cfqueryparam. I had been using CreateDate() to create my start/end dates like this:

<cfset StartDate = CreateDate(2012,5, 1)>

I was using it in my SQL WHERE in the query of query like this:

WHERE PublishedDate >=  <cfqueryparam value="#StartDate#" cfsqltype="cf_sql_date">

It was cf_sql_date. It should be cf_sql_timestamp The date was capping it to 5-31, and thus anything posted later on in the day on 5-31 was omitted. The minutes were dropping.

Thanks for everyone's input.

Fixing legacy code rocks!

Upvotes: 3

Rahul
Rahul

Reputation: 77876

Try comparing by removing the timepart from datetime. So your between should look like

WHERE PublishedDate BETWEEN '2012-05-01' AND '2012-05-31'

Upvotes: 1

Turnkey
Turnkey

Reputation: 9406

You didn't say which database you're using but if it's ODBC or MSSQL I usually do something like this:

WHERE PublishedDate >= '2012-05-01'
AND PublishedDate < '2012-06-01'

Basically you add one day to the end date that you want to include and use the less than operator. Leaving off the times prevents any oddities with how the date is internally constructed.

Upvotes: 3

Related Questions