CODEWITHSUNDEEP
sql-servert-sql
dutchlab
dutchlab

Reputation: 590

SQL Server - Check date field between two dates in where clause

In the following query the date returned is 2019-07-12 14:12:58.253

 SELECT MAX(fileDate) AS maxdate FROM filetable

This query returns the following value 2019-07-11 23:46:20.317

 SELECT MAX(fileDate) AS maxdate FROM filetable WHERE fileDate BETWEEN '2019-01-18' AND '2019-07-12'

I have tried using >= and <= instead of BETWEEN with the same results.

Why is this happening?

Upvotes: 0

Views: 42

Answers (1)

Thom A
Thom A

Reputation: 95554

'2019-07-12' against a datetime will be implicitly converted to the datetime 2019-07-12T00:00:00.000. For your query with the WHERE clause fileDate BETWEEN '2019-01-18' AND '2019-07-12' that means that a value like 2019-07-12T14:12:58.253 is outside of the range, as it's larger than 2019-07-12T00:00:00.000.

The common way is to use >= and < where the value for the < is the day after the day you need. Therefore you end up with the below:

SELECT MAX(fileDate) AS maxdate
FROM filetable
WHERE fileDate >= '2019-01-18'
  AND fileDate < '2019-07-13';

Upvotes: 1

Related Questions