Problem with date query

1

Hi, I have the below t-sql. It is returning no rows, but I know there are rows for that timeframe. If I hardcode the date values in place of the variables, it works as expected. What am I doing wrong?

Your help is greatly appreciated.

declare @startdate DATE = '2020-01-01 07:00:00.000'
declare @enddate DATE = '2020-01-01 15:00:00.000'

SELECT incidentno, code1,code2,dtrecv
FROM table1
where (code1 in ('13-0','13-1','32-0','31-0','55-0')
or (code1 = '68-0' and ltrim(code2) = 'O'))
and dtrecv between @startdate and @enddate
order by dtrecv

2

Please take a look at the difference between date and datetime fields

without your data .. its just a guess ..

It could be several reasons out of which we have to narrow it down to yours

3

That's it friend! Changing them to datetime datatype did the trick!

Thanks a bunch.

4

I recommand that you watch this video of Pinal Dave for something I didn't know about using between :wink:

5

@RogierPronk is spot on. BETWEEN includes both end points and it's almost always a problem.

The fix is to not use an EndDate as an inclusive date in the criteria. It should be used more as a CUTOFF date and time that represents the first instant to NOT BE INCLUDED.

In the original example, the desired times are 17:00 TO 15:00 and probably not 17:00 THRU 15:00, if you understand the slight difference there as being that you don't actually want anything returned from 15:00 onward. In other words, 15:00 is being used as a CUTOFF date here.

If that's true, then the following code (look at the comments where I explain), will do the trick.

declare @startdate DATETIME = '2020-01-01 07:00:00.000'
        ,@enddate  DATETIME = '2020-01-01 15:00:00.000' --Cutoff time

SELECT incidentno, code1,code2,dtrecv
FROM table1
where (code1 in ('13-0','13-1','32-0','31-0','55-0')
or (code1 = '68-0' and ltrim(code2) = 'O'))
-- and dtrecv between @startdate and @enddate     --OLD INCORRECT METHOD
-- and dtrecv >= startdate and dtrecv <= @enddate --THIS IS WHAT BETWEEN IS EQUAL TO
   and dtrecv >= startdate and dtrecv <  @enddate --THIS IS THE RIGHT WAY TO DO IT
order by dtrecv

If you want to include all of the times in the 15:00 hour, then you'd do something like the following where we figure hour what the first out to NOT INCLUDE is and that will eliminate the problem and Pinal Dave cited as the 23:59:59.997 problem.

declare @startdate DATETIME = '2020-01-01 07:00:00.000'
        ,@enddate  DATETIME = '2020-01-01 15:00:00.000' --Last hour to include

SELECT incidentno, code1,code2,dtrecv
FROM table1
where (code1 in ('13-0','13-1','32-0','31-0','55-0')
or (code1 = '68-0' and ltrim(code2) = 'O'))
and dtrecv >= startdate and dtrecv < DATEADD(hh,1,@enddate) --THIS IS THE RIGHT WAY TO DO IT
order by dtrecv

Using the first example of code above, you might want to make how the OR is actually being use a bit more obvious, be consistent in capitialization, and use some indentation just to make it all a bit easier to read. It's also a really good practice to always use the 2 part naming convention for all objects and not using semi-colons has been deprecated since 2005. There's going to be hell to pay for everyone that hasn't been using them for the last nearly 2 decades if they ever decide to enforce that deprecation. For example...

DECLARE @StartDate DATETIME = '2020-01-01 07:00:00.000'
        ,@EndDate  DATETIME = '2020-01-01 15:00:00.000'
;
 SELECT incidentno, code1, code2, dtrecv
   FROM dbo.Table1
  WHERE (
            code1 IN ('13-0','13-1','32-0','31-0','55-0')
        OR (code1 = '68-0' AND LTRIM(code2) = 'O')
        )
    AND dtrecv >= @StartDate AND dtrecv < @EndDate
  ORDER BY dtrecv
;

It's your code so you can do what you want for formatting. I'm just making a suggestion on how to make more friends and influence your enemies. :rofl: