What would be the preferred method for speed and less database stress using a between or less than?

FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) BETWEEN '2019-01-01' AND '2019-12-31'

SET @StartLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000')
FROM [Master].[dbo].log mlog
WHERE LogDate < '2019-12-31T00:00:00.000'
  • If you need help making the queries faster, then you need to add a minimal reproducible example to your question so we can see all the details about the table in question, including columns, indexes, statistics objects, etc. – Max Vernon Jan 10 at 21:19
  • But, honestly, that should really be a new question, since this question is about whether to use between or less than. – Max Vernon Jan 10 at 21:20

My personal preference is to never use BETWEEN since its not abundantly clear, from the code itself, whether the range includes 2019-01-01 and 2019-12-31. I realize one can easily check the manual to see how BETWEEN operates, but in my opinion, this is far more easily read, without any chance for confusion:

FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) >= '2019-01-01' 
    AND cast(LogDate as Date) <= '2019-12-31'

Be aware, though, that cast(LogDate as date) is not an optimal way to perform this query. The far better solution would be:

FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000' 
    AND LogDate < '2020-01-01T00:00:00.000'

The above statement is SARGable, and will also ensure you get the range desired. Comparing <= '2019-12-31' actually means before December 31st, 2019, when you probably actually mean up until the end-of-day on December 31st, 2019. Looking for dates less than the start of the next day ensures you get all the rows between the start of the day on 2019-01-01 and the end of the day on 2019-12-31.

Note, I'm including a T between the date and time components; this indicates to SQL Server to treat the value as an ISO8601 date, which will prevent possible errors that might be introduced under non-US English language installations.

|improve this answer|||||
  • thanks for the help I have updated the query would I use the same process? – Jefferson Jan 10 at 20:34
  • @Jefferson - sorry, it's unclear what you are asking me. – Max Vernon Jan 10 at 21:17
  • Query 2 and 3 are using the id of the start and end is that a better way? – Jefferson Jan 10 at 21:55
  • @Jefferson Better for what? – Max Vernon Jan 10 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.