missing one record when selecting by date range

Question

missing one record when selecting by date range

itortu

SSCommitted

Points: 1914
More actions
May 26, 2015 at 8:44 am

#300967

When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
Many thanks.

Viewing 15 posts - 1 through 15 (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

itortu (5/26/2015)
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
Many thanks.

This:

SELECT orderno, *

FROM _order

WHERE order_date >= '20150514 00:00:00'

AND order_date < '201505116 00:00:00'

ORDER BY order_date asc

itortu SSCommitted Points: 1914 More actions · Answer 2

This did the trick for me:

SELECT orderno
FROM _order
WHERE CAST(FLOOR(CAST(order_date AS FLOAT)) AS DATETIME)
BETWEEN CAST('5/14/2015' AS DATETIME)
AND CAST('5/15/2015' AS DATETIME)
ORDER BY order_date asc

The date will come always in the format mm/dd/yyyy

Many thanks

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

itortu (5/26/2015)
This did the trick for me:
SELECT orderno
FROM _order
WHERE CAST(FLOOR(CAST(order_date AS FLOAT)) AS DATETIME)
BETWEEN CAST('5/14/2015' AS DATETIME)
AND CAST('5/15/2015' AS DATETIME)
ORDER BY order_date asc
The date will come always in the format mm/dd/yyyy
Many thanks

This will return all records from 05/14/2015 00:00:00 up to AND including 05/15/2015 00:00:00.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

itortu (5/26/2015)
This did the trick for me:
SELECT orderno
FROM _order
WHERE CAST(FLOOR(CAST(order_date AS FLOAT)) AS DATETIME)
BETWEEN CAST('5/14/2015' AS DATETIME)
AND CAST('5/15/2015' AS DATETIME)
ORDER BY order_date asc
The date will come always in the format mm/dd/yyyy
Many thanks

This would prevent index seeks on order_date column.

Lynn's solution would be a preferred method, you just need to remove an extra 1 that was included due to a typo.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

itortu SSCommitted Points: 1914 More actions · Answer 5

How would I need to change the query in order to be able and use a date format like MM/DD/YYYY?

Many thanks.

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

Did you try your original query with '5/15/2015 23:59:59' Instead of '5/15/2015 11:59:59'?

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]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

The safe way would be to always manage dates as dates instead of strings.

To safely convert a string into a date, you can use CONVERT (or TRY_CONVERT if available), as it will allow you to use format codes.

SET dateformat mdy --Testing dateformat options

SELECT orderno, *

FROM _order

WHERE order_date >= CONVERT( datetime, '05/14/2015', 101)

AND order_date < CONVERT( datetime, '05/16/2015', 101)

ORDER BY order_date asc

SET dateformat dmy --Testing dateformat options

SELECT orderno, *

FROM _order

WHERE order_date >= CONVERT( datetime, '05/14/2015', 101)

AND order_date < CONVERT( datetime, '05/16/2015', 101)

ORDER BY order_date asc

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

Lynn Pettis (5/26/2015)
itortu (5/26/2015)
This did the trick for me:
SELECT orderno
FROM _order
WHERE CAST(FLOOR(CAST(order_date AS FLOAT)) AS DATETIME)
BETWEEN CAST('5/14/2015' AS DATETIME)
AND CAST('5/15/2015' AS DATETIME)
ORDER BY order_date asc
The date will come always in the format mm/dd/yyyy
Many thanks
This will return all records from 05/14/2015 00:00:00 up to AND including 05/15/2015 00:00:00.

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;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

itortu SSCommitted Points: 1914 More actions · Answer 9

I changed my original query to use 23:59:59, and that also gave me the missing record back.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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

You're just asking for a world of hurt in the future. I strongly urge you get out of that bad habit and always use the >= and < methods previously demonstrated so that your code becomes bullet proof for now and in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

SQL reads 11:59:59 AS AM. The the missing record was PM.

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]

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

Jeff Moden (5/26/2015)
itortu (5/26/2015)
I changed my original query to use 23:59:59, and that also gave me the missing record back.
You're just asking for a world of hurt in the future. I strongly urge you get out of that bad habit and always use the >= and < methods previously demonstrated so that your code becomes bullet proof for now and in the future.

Agree.

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]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 13

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho