Pulling incorrect records using date range in where clause

Question

Post reply

Pulling incorrect records using date range in where clause

daniness

SSCrazy

Points: 2890
More actions
April 22, 2014 at 3:04 pm

#284803

Hi All,
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select *
from dbo.ACCTING_TRANSACTION_hISTORY
where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and
ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling reocrds from 2009, etc. and I'm not sure why. I appreciate your insight. Thanks.

Viewing 15 posts - 1 through 15 (of 18 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The AND operator has a higher precedence than the OR operator leading to the following logic:

where (ath_postype = 'NTC') or (ath_postype='NTD' and

ath_postdate >= '2013-01-01 00:00:00' and

ath_postdate <= '2013-01-05 23:59:59')

If this is not your intention, I'd recommend using parenthesis:

where (ath_postype = 'NTC' or ath_postype='NTD')

and ath_postdate >= '2013-01-01 00:00:00'

and ath_postdate <= '2013-01-05 23:59:59'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

daniness SSCrazy Points: 2890 More actions · Answer 2

daniness

SSCrazy

Points: 2890

April 23, 2014 at 7:09 am

#1708080

Thanks, LutzM. That did the trick. 🙂

ScottPletcher SSC Guru Points: 101105 More actions · Answer 3

Btw, you should < on date/datetime, not <=, as below. Otherwise you risk missing rows, especially if the data type of the underlying column changes (to datetime from smalldate, datetime2 from datetime, etc.).

Also, 'YYYYMMDD' is the only 100% safe date format (excluding obscure versions with 'T' in them).

ath_postdate >= '20130101' and

ath_postdate < '20130106'

Edit: I'm assuming it was Jan 05 not May 01 (demonstrating the ambiguity of the other format :-)).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

below86 SSChampion Points: 11532 More actions · Answer 4

Wouldn't it be better to use the BETWEEN with these dates?

WHERE (ath_postype = 'NTC' OR ath_postype='NTD')

AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

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

below86 (4/23/2014)
Wouldn't it be better to use the BETWEEN with these dates?
WHERE (ath_postype = 'NTC' OR ath_postype='NTD')
AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'

And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?

below86 SSChampion Points: 11532 More actions · Answer 6

Lynn Pettis (4/23/2014)
below86 (4/23/2014)
Wouldn't it be better to use the BETWEEN with these dates?
WHERE (ath_postype = 'NTC' OR ath_postype='NTD')
AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'
And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?

I knew that was coming as soon as I hit 'Post'. 🙂 In our case, at least as of right now, the dates don't carry the '.133'. I guess I could just change it to '2013-01-05 23:59.59.999'. My question was more towards using the BETWEEN than coding it with the >= and <=.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

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

below86 (4/23/2014)
Lynn Pettis (4/23/2014)
below86 (4/23/2014)
Wouldn't it be better to use the BETWEEN with these dates?
WHERE (ath_postype = 'NTC' OR ath_postype='NTD')
AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'
And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?
I knew that was coming as soon as I hit 'Post'. 🙂 In our case, at least as of right now, the dates don't carry the '.133'. I guess I could just change it to '2013-01-05 23:59.59.999'. My question was more towards using the BETWEEN than coding it with the >= and <=.

Best way to query a range of dates is to use a closed end on the lower end (SomeDateCol >= '20140101') and an open end on the upper end of the range (< '20140106').

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

below86 (4/23/2014)
Lynn Pettis (4/23/2014)
below86 (4/23/2014)
Wouldn't it be better to use the BETWEEN with these dates?
WHERE (ath_postype = 'NTC' OR ath_postype='NTD')
AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'
And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?
I knew that was coming as soon as I hit 'Post'. 🙂 In our case, at least as of right now, the dates don't carry the '.133'. I guess I could just change it to '2013-01-05 23:59.59.999'. My question was more towards using the BETWEEN than coding it with the >= and <=.

No, you can't accurately change it to "23:59.59.999" either, because that rounds up to 00:00:00.000.

That's why I urged earlier, and others also, you should always use "<" on the ending range.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

below86 SSChampion Points: 11532 More actions · Answer 9

Good to know, I wasn't aware that it rounded up.

It is wierd that if you do .998 it shows as .997.

declare @d datetime;

set @d = '2014-04-23 23:59:59.998'

select @d

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

daniness SSCrazy Points: 2890 More actions · Answer 10

Thanks for all the helpful input, everyone. 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

below86 (4/23/2014)
Good to know, I wasn't aware that it rounded up.
It is wierd that if you do .998 it shows as .997.
declare @d datetime;
set @d = '2014-04-23 23:59:59.998'
select @d

0

This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...

Datetime in BOL

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

below86 SSChampion Points: 11532 More actions · Answer 12

Matt Miller (#4) (4/23/2014)
below86 (4/23/2014)
Good to know, I wasn't aware that it rounded up.
It is wierd that if you do .998 it shows as .997.
declare @d datetime;
set @d = '2014-04-23 23:59:59.998'
select @d
0
This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...
Datetime in BOL

So If I change my code to use the .998 I would never miss any records, since a record would never have .999, it would be rounded up to the next day.

(YYYY-MM-DD)

BETWEEN '2014-04-01 00:00:00.000' AND '2014-04-30 23:59:59.998'

This would get all possible records where this date is in April 2014.

I know we have between coded in some area's, but I think that is after the data is loaded to our warehouse, and I 'm pretty sure the ETL strips off the ms. (Didn't set it up, so I don't know why.)

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

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

below86 (4/23/2014)
Matt Miller (#4) (4/23/2014)
below86 (4/23/2014)
Good to know, I wasn't aware that it rounded up.
It is wierd that if you do .998 it shows as .997.
declare @d datetime;
set @d = '2014-04-23 23:59:59.998'
select @d
0
This comes from the fact that datetime is accurate within 3.333ms. So .995 rounds up to .997, .992 rounds up to .993 , etc...
Datetime in BOL
So If I change my code to use the .998 I would never miss any records, since a record would never have .999, it would be rounded up to the next day.
(YYYY-MM-DD)
BETWEEN '2014-04-01 00:00:00.000' AND '2014-04-30 23:59:59.998'
This would get all possible records where this date is in April 2014.
I know we have between coded in some area's, but I think that is after the data is loaded to our warehouse, and I 'm pretty sure the ETL strips off the ms. (Didn't set it up, so I don't know why.)

Until you change data types from datetime to datetime2 where the accuracy increases to 100 micro seconds. Again, for selecting records for a range of date time values using a range that is closed at the lower end and open ended at the upper end (somedatecol >= '20140101' and somedatecol < '20140201' to return all records in January 2014) is really the way to go.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 14

below86 (4/23/2014)
Lynn Pettis (4/23/2014)
below86 (4/23/2014)
Wouldn't it be better to use the BETWEEN with these dates?
WHERE (ath_postype = 'NTC' OR ath_postype='NTD')
AND ath_postdate BETWEEN '2013-01-01 00:00:00.000' AND '2013-01-05 23:59:59.000'
And what happens if the ath_postdate contains the value '2013-01-05 23:59:59.133'?
I knew that was coming as soon as I hit 'Post'. 🙂 In our case, at least as of right now, the dates don't carry the '.133'. I guess I could just change it to '2013-01-05 23:59.59.999'. My question was more towards using the BETWEEN than coding it with the >= and <=.

As others have already stated, that would actually round up to 2013-01-06 if the column were a DATETIME column because the resolution of DATETIME is 3.3ms. The BEST way (and I don't say that often) is to use the method that Scott and Lynn have both suggested. That way, it doesn't matter what the underlying DT datatype is... ever.

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