Date time cast preferred method

Question

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

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

Or, would it be better to do this?

SET @StartLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000')

SET @EndLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate < '2019-12-31T00:00:00.000'
ORDER BY LogDate DESC);

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE mlog.LogId >= @StartLogId
    AND mlog.LogId <= @EndLogId;
```

Answer 1

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:

SELECT LogId
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:

SELECT LogId
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.