I am using this code:
having MIN(start_DATE::timestamp) between DATEADD('MONTH',-1,DATE_TRUNC('MONTH',GETDATE())) and DATEADD('DAY',-1, DATEADD('MONTH',+2,DATE_TRUNC('MONTH',GETDATE())))
I am noticing that it is not picking up all the times that fall within the range. I am pulling dates 8/1-10/31, but it is exlcuding records due to the time associated with the date. For example 2024-08-01 00:50:00.000 Z and 2024-10-31 20:59:00.000 Z are not picked up.
How can I update the date functions to encompass all the times, or just ignore the times and only pull based on the dates?
If you run this query on 25-Sep-2024 then, and if the MIN (start_Date is '2024-10-31 20:59:00.000 Z', the expression will boil down to:
having '2024-10-31 20:59:00.000' between '2024-08-01 00:00:00.000' and '2024-10-31 00:00:00.000'
2024-10-31 20:59:00.000 is greater (later) than the end-point you calculated; so it doesn't satisfy.
To get around it, you can remove the time component of start_Date (you can cast it to date, instead of timestamp); or change the end-point to '2024-10-31 23:59:59.999'.
There are other ways like using < 2024-11-01, instead of BETWEEN.
Once you understand that the issue is related to the left-hand operand having non-zero time-component, I believe you can solve it yourself.