0

Maybe I'm making an obvious mistake but can anyone explain what's going on here? I was running a query where the table's field is datetime and the query I was running was something like

SELECT *
FROM Table
WHERE DateTimeColumn <= '20170714'

and I noticed the output excluded the records where DateTimeColumn is '20170714' they finished at '20170713'

Below I was expecting all 3 IIF to fall into true.

DECLARE @d1 DATE = '20170714'

SELECT IIF(GETDATE() <= @d1, 'GETDATE() Less than or equal to @d1', 'GETDATE() **NOT** Less than or equal to @d1')

DECLARE @d2 DATE = '20170714 11:59:59'

SELECT IIF(GETDATE() <= @d2, 'GETDATE() Less than or equal to @d2', 'GETDATE() **NOT** Less than or equal to @d2')

DECLARE @tomorrow DATE = '20170715'

SELECT IIF(GETDATE() <= @tomorrow, 'GETDATE() Less than or equal to @tomorrow', 'GETDATE() **NOT** Less than or equal to @tomorrow')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
curious
  • 163
  • 2
  • 11
  • `<= '20170714 00:00:00'` obviously excludes datetimes greater than that – juergen d Jul 14 '17 at 01:07
  • @juergend How about @d2? – curious Jul 14 '17 at 01:10
  • are you sure you have DateTimeColumn **20170713** ? have you try order by **DateTimeColumn** to see it clearly? – Denny Sutedja Jul 14 '17 at 01:11
  • @DennySutedja Yes, they stop at 20170713 – curious Jul 14 '17 at 01:13
  • sorry i mean DateTimeColumn **20170714** if you have it, can you show us a little sample of your data. – Denny Sutedja Jul 14 '17 at 01:15
  • 2
    Note: What is happening with @d2 is you're converting '20170714 11:59:59' to a `DATE` instead of a `DATETIME`, making it become equivalent to '20170714 00:00:00' (because a `DATE` data type removes the time component, meaning if you convert it back to a `DATETIME` the time component will be 0 - making it 00:00:00.000)... Be careful when comparing dates and datetimes. – ZLK Jul 14 '17 at 01:28
  • You realize you are setting a datetime to a date variable right??? – S3S Jul 14 '17 at 03:09
  • Possible duplicate of [T-SQL Between Dates Confusion](https://stackoverflow.com/questions/5434001/t-sql-between-dates-confusion) – GSerg Jul 14 '17 at 07:19

2 Answers2

0

Just use less than 2017-07-15 (tomorrow)

SELECT *
FROM Table
WHERE DateTimeColumn < '20170715'

If wanting to use getdate, try this:

SELECT *
FROM Table
WHERE DateTimeColumn < dateadd(day,1,cast(getdate() as date))

Use sargable predicates. DO NOT convert your data to suit a filtering predicate, this affects index access and/or requires unnecessary calculations. Here is a former answer on the similar question.

Also note that 23:59:59 is NOT the end of a day, it is one full second short of a full day: datetime is accurate to approx 3 milliseconds and datetime2 is even more sensitive.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • `where cast(DateTimeColumn as date) <= cast(getdate() as date)` will also work, and it is sargable. – GSerg Jul 14 '17 at 07:21
  • @GSerg only sargable in some recent versions of SQL Server. It isn't a typically sargable predicate where the rule of thumb is: don't apply functions on data, and for those who are learning "sargable" it is way easier to stick to that rule. In any case using cast() on data is unnecessary calculation (as already stated) as there is a perfectly simple alternative that does not require casting data at all. – Paul Maxwell Jul 14 '17 at 07:28
-1

Can you just change the query like this :

SELECT *
  FROM Table
  WHERE CONVERT(date, DateTimeColumn) <= '20170714'

It would return all the records less that 14 and record with date 14.

pritesh agrawal
  • 1,155
  • 8
  • 16
  • Actually I have tried that and it doesn't work. But I couldn't create sample code that shows the problem. `WHERE CONVERT(date, DateTimeColumn) <= '20170714'` excludes rows where the column is sometime today but when I use `WHERE CONVERT(date, DateTimeColumn) <= '20170714 11:59:59'` that works. – curious Jul 14 '17 at 01:24
  • Sorry, I meant `'20170714 23:59:59'` – curious Jul 14 '17 at 01:29
  • Can you share some rows from the table , I doubt if you are storing time in something like UTC. – pritesh agrawal Jul 14 '17 at 01:31