missing one record when selecting by date range

Question

missing one record when selecting by date range

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Eric M Russell (5/26/2015)
Luis Cazares (5/26/2015)
Eric M Russell (5/26/2015)
A simple CAST operation from date/time to date won't necessarily prevent usage of an index on a date/time column. For example, given the following table and index, both queries leveraged the index [ix_create_date] according to the Actual Execution Plan.
select o.object_id, o.create_date, m.definition
into #T
from sys.objects o
join sys.all_sql_modules m on m.object_id = o.object_id;
create index ix_create_date on #T (create_date);
select object_id
from #T
where cast(create_date as date) between '2013/01/01' and '2013/12/31'
order by create_date;
select object_id
from #T
where create_date >= '2013/01/01' and create_date < '2014/01/01'
order by create_date;
This is valuable information, Eric. However, the OP used a FLOOR between 2 CASTs and I'm sure that won't be the same case.
Yeah, I don't get why his date conversion also included the FLOOR function; all it needs is to CAST order_date as Date.

Because he was casting back to a DATETIME data type. Only thing I could think of here.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

itortu (5/26/2015)
I changed my original query to use 23:59:59, and that also gave me the missing record back.

But you'll still miss 23:59:59.006. The alternative suggested with a < on the upper boundary won't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Alvin Ramard (5/26/2015)
Did you try your original query with '5/15/2015 23:59:59' Instead of '5/15/2015 11:59:59'?

FYI, I only asked this question to show the OP why the missing record was excluded, not to suggest this as best practice.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]