Between conditions with dates has odd behavior

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.

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

There are three SELECTs. 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 DATETIMEs 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 DATETIMEs 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 DATETIMEs on those two days are returned.

Similarly when we explicitly cast the DATETIMEs to a DATE and filter by date we get all the rows for both dates.