SQL Server Between conditions with dates has odd behavior

cclgggtu  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(110)

I have this T-SQL query in SSMS:

DECLARE @TodaysDate datetime
DECLARE @AsAtDate DATETIME

SET @TodaysDate = GETDATE()

PRINT (DATEPART(WEEKDAY, @TodaysDate))

IF DATEPART(WEEKDAY, @TodaysDate)  = 2              -- is today Monday?
    SET @AsAtDate = DATEADD(DAY, -3, @TodaysDate)       -- Report contains Friday, Saturday & Sunday
ELSE
    SET @AsAtDate = DATEADD(DAY, -1, @TodaysDate)       -- Report contains last working day

SELECT 
    * 
FROM
    (SELECT 
         --app.short_id, 
         CONVERT(VARCHAR(11), app.created_at, 105) AS [Date Created],
         CONVERT(VARCHAR(11), stat.activity_created, 105) AS [Date Updated],
     FROM
         Table1 app
     LEFT JOIN
         Table2 stat ON app.appID = stat.appID
     WHERE
         stat.activity_created BETWEEN @AsAtDate AND @TodaysDate) T

which doesn't seem to return any data.

but if I change:

SET @AsAtDate = DATEADD(DAY, -1, @TodaysDate)

to:

SET @AsAtDate = DATEADD(DAY, -2, @TodaysDate)

it returns some, and the supposed data that should have been returned even if I didn't have to change the code above. For context, today is September 15th, it should have returned 14th.

sql-server

1条答案

按热度按时间
rjzwgtxy

rjzwgtxy1#

This might be helpful in demonstrating the potential issue you're seeing.

There are three SELECT s. The first returns values from a DATETIME column where it's between two dates (which are implicitly converted to date times, with the time portion set to 00:00:00.000 ). The second uses a specific time portion in the WHERE . Finally the DATETIME s are cast to DATE and compared to DATE .

;WITH DateTimes AS (
SELECT GETDATE() AS DateTime
UNION ALL
SELECT DATEADD(HOUR,1,DateTime)
  FROM DateTimes
 WHERE DateTime < DATEADD(DAY,2,GETDATE())
), Dates AS (
SELECT CAST(DateTime AS DATE) AS Date
  FROM DateTimes
)

SELECT *, '2023-09-15 AND 2023-09-16'
  FROM DateTimes
 WHERE DateTime BETWEEN '2023-09-15' AND '2023-09-16'
UNION ALL
SELECT *, '2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997'
  FROM DateTimes
 WHERE DateTime BETWEEN '2023-09-15 00:00:00.000' AND '2023-09-16 23:59:59.997'
UNION ALL
SELECT *, '2023-09-15 AND 2023-09-16'
  FROM Dates
 WHERE Date BETWEEN '2023-09-15' AND '2023-09-16';

You can see the difference in the results. In the first the DATETIME s filtered by a date don't include anything after midnight on the end date, because it's not between the two literals.

When we add the time, we now get the rows expected, all the DATETIME s on those two days are returned.

Similarly when we explicitly cast the DATETIME s to a DATE and filter by date we get all the rows for both dates.
| DateTime | (No column name) |
| ------------ | ------------ |
| 2023-09-15 09:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 10:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 11:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 12:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 13:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 14:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 15:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 16:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 17:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 18:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 19:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 20:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 21:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 22:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 23:32:45.210 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 09:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 10:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 11:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 12:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 13:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 14:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 15:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 16:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 17:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 18:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 19:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 20:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 21:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 22:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 23:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 00:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 01:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 02:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 03:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 04:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 05:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 06:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 07:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 08:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 09:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 10:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 11:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 12:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 13:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 14:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 15:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 16:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 17:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 18:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 19:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 20:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 21:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 22:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-16 23:32:45.210 | 2023-09-15 00:00:00.000 AND 2023-09-16 23:59:59.997 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-15 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |
| 2023-09-16 00:00:00.000 | 2023-09-15 AND 2023-09-16 |

赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com