Using >= or <= with DateTime parameter not working?

Question

Using >= or <= with DateTime parameter not working?

phoeneous 65427

Old Hand

Points: 385
More actions
October 21, 2014 at 12:07 pm

#311957

Trying to filter either directly from query or from ssrs and for some reason, the @begindate and @enddate parameters are not including the first and last days.
For example:
select * from orders where orderdate >= @begindate AND orderdate <= @enddate
If I choose 09/01/2014 for @begindate and 09/30/2014 as @enddate only the records from 09/02 to 09/29 are included, any orders on 09/01 or 09/30 are not retrieved. I've also tried using between but same results. Am I using the operators incorrectly?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

This sounds like it's due to the time. When you pass your parameters as 09/01/2014 and 09/30/2014 and then query using them, a row with a date of 09/30/2014 23:00:00.000 won't be included. The value 09/30/2014 as a datetime is treated as 09/01/2014 00:00:00.000.

To make it work, declare a local variable of a datetime data type. Then assign the passed date and add the time. Your query with the local datetime variable will then return the rows you expect. Example:

DECLARE @dtmEnd Datetime;

SET @dtmEnd = @enddate + ' 23:59:59.997';

Note that you want to use .997 as the precision for the time part because .999 will round to the next day and therefore include any rows from the next day exactly at midnight or those without a time value.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

phoeneous 65427 Old Hand Points: 385 More actions · Answer 2

It works through query, but how can I apply 23:59:59 to @enddate in ssrs 2008?

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

phoeneous 65427 (10/21/2014)
It works through query, but how can I apply 23:59:59 to @enddate in ssrs 2008?

Well, since about the only thing I know about SSRS is how to spell it :-P, I'm going to have to let someone else handle this from here.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

phoeneous 65427 Old Hand Points: 385 More actions · Answer 4

This works when I apply it to the query, no need to filter in ssrs.

AND (OrderDate BETWEEN (@StartDate ) AND (@EndDate + '23:59:59'))

LinksUp SSCertifiable Points: 6526 More actions · Answer 5

phoeneous 65427 (10/21/2014)
This works when I apply it to the query, no need to filter in ssrs.
AND (OrderDate BETWEEN (@StartDate ) AND (@EndDate + '23:59:59'))

I have never been a big fan of doing queries with a time component when it is not used or not needed.

If your data types are all datetime, then the following query will get all the rows in the range. If it does not, then something else is wrong.

AND OrderDate >= @StartDate and OrderDate < DATEADD(dd, 1, @EndDate)

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

PhilPacha Hall of Fame Points: 3645 More actions · Answer 6

Trying to add an arbitrary amount of 'time' to a datetime value which starts out containing ONLY a date is fraught with problems. Try adding the last millisecond or two, for example. The way that is ALWAYS safe is to use >= @YourStartDate and < DATEADD(DAY, 1, @YourEndDate) as your range.

My apologies for not noticing my typographical error, before posting. Of *course* the comparison to the DATEADD(...) should only be less than.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

PhilPacha (10/21/2014)
Trying to add an arbitrary amount of 'time' to a datetime value which starts out containing ONLY a date is fraught with problems. Try adding the last millisecond or two, for example. The way that is ALWAYS safe is to use >= @YourStartDate and <= DATEADD(DAY, 1, @YourEndDate) as your range.

Unless the datetime column you're reading contains a date matching DATEADD(day, 1 @YourEndDate) without the time, in which case it'll include the row when it shouldn't. You'll have to use < instead of <=.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

phoeneous 65427 (10/21/2014)
This works when I apply it to the query, no need to filter in ssrs.
AND (OrderDate BETWEEN (@StartDate ) AND (@EndDate + '23:59:59'))

That misses a full second. Always round down to the whole day and then add a full day and check for "<" for the end date.

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

PhilPacha (10/21/2014)
Trying to add an arbitrary amount of 'time' to a datetime value which starts out containing ONLY a date is fraught with problems. Try adding the last millisecond or two, for example. The way that is ALWAYS safe is to use >= @YourStartDate and <= DATEADD(DAY, 1, @YourEndDate) as your range.

Ah, be careful now. In most cases, you only want LESS THAN for the end date +1. You also have to make sure that @YourEndDate contains only a midnight time component unless its the DATE datatype.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT * FROM orders WHERE CAST(orderdate AS DATE) BETWEEN @begindate AND @enddate

http://sqlblog.com/blogs/rob_farley/archive/2010/01/22/sargable-functions-in-sql-server.aspx

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PhilPacha Hall of Fame Points: 3645 More actions · Answer 11

Sorry, everyone! I've removed the typo from my earlier post. Stupid fingers!