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?
Amy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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.
1
Tinazmu’s answer is very correct.
A larger example of truncating the value being tested, OR changing the end bound to be the next day, and added a less-than clause:
select
$1::timestamp as dt,
to_timestamp_ntz(GETDATE()) as cur_date,
DATEADD('MONTH',-1,DATE_TRUNC('MONTH',cur_date::date)) as start_dt,
DATEADD('DAY',-1, DATEADD('MONTH',+2,DATE_TRUNC('MONTH',cur_date::date))) as end_dt,
dt between start_dt and end_dt as current_answer,
dt::date between start_dt and end_dt as value_truncated,
DATEADD('MONTH',+2,DATE_TRUNC('MONTH',cur_date::date)) as next_dt,
dt between start_dt and next_dt and dt < next_dt as less_than_next
from values
('2024-08-23 00:01:23'),
('2024-09-25 00:01:23'),
('2024-10-31 00:00:00'),
('2024-10-31 00:00:01') -- missed
;
| DT | CUR_DATE | START_DT | END_DT | CURRENT_ANSWER | VALUE_TRUNCATED | NEXT_DT | LESS_THAN_NEXT |
|---|---|---|---|---|---|---|---|
| 2024-08-23 00:01:23.000 | 2024-09-24 19:22:48.541 | 2024-08-01 | 2024-10-31 | TRUE | TRUE | 2024-11-01 | TRUE |
| 2024-09-25 00:01:23.000 | 2024-09-24 19:22:48.541 | 2024-08-01 | 2024-10-31 | TRUE | TRUE | 2024-11-01 | TRUE |
| 2024-10-31 00:00:00.000 | 2024-09-24 19:22:48.541 | 2024-08-01 | 2024-10-31 | TRUE | TRUE | 2024-11-01 | TRUE |
| 2024-10-31 00:00:01.000 | 2024-09-24 19:22:48.541 | 2024-08-01 | 2024-10-31 | FALSE | TRUE | 2024-11-01 | TRUE |
My personal preference is the less than version. Because albeit that I have truncated the values to date so the results tables are not really wide, the answer works just the same, and it means if you are in the second before midnight, it still works etc etc, which feels cleaner to me.