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 aDATETIMEcolumn where it's between two dates (which are implicitly converted to date times, with the time portion set to00:00:00.000). The second uses a specific time portion in theWHERE. Finally theDATETIMEs are cast toDATEand compared toDATE.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 aDATEand filter by date we get all the rows for both dates.