Getting 0 results when I use BETWEEN DATEADD(MONTH,-4,CURRENT_DATE) AND DATEADD(MONTH,-1,CURRENT_DATE)

Question

I am trying to get last 3 months data from the OTO_HISTORICAL_TRACKING_DETAIL CTE that I have created.

OTO_START_DATE is in DATE format - 'YYYY-MM-DD'.

This query returns 0 results

SELECT*
FROM OTO_HISTORICAL_TRACKING_DETAIL
WHERE OTO_START_DATE BETWEEN ADD_MONTHS(CURRENT_DATE, -4) 
                         AND ADD_MONTHS(CURRENT_DATE, -1)

However, when I use this query, I get results

SELECT *
FROM OTO_HISTORICAL_TRACKING_DETAIL
WHERE OTO_START_DATE BETWEEN ADD_MONTHS(CURRENT_DATE, -4) 
                         AND CURRENT_DATE

Answer 1

So lets get some trival data to check the logic:

with OTO_HISTORICAL_TRACKING_DETAIL(OTO_START_DATE) as (
    select * from values 
    ('2023-08-01'::date),
    ('2023-09-01'::date),
    ('2023-10-01'::date),
    ('2023-11-01'::date),
    ('2023-12-01'::date)   
)
SELECT *
FROM OTO_HISTORICAL_TRACKING_DETAIL
WHERE OTO_START_DATE BETWEEN ADD_MONTHS(CURRENT_DATE, -4) 
                         AND ADD_MONTHS(CURRENT_DATE, -1)

gives:

Lets workout what could be happening by looking at all the values:

with OTO_HISTORICAL_TRACKING_DETAIL(OTO_START_DATE) as (
    select * from values 
    ('2023-06-01'::date),
    ('2023-07-01'::date),
    ('2023-08-01'::date),
    ('2023-09-01'::date),
    ('2023-10-01'::date),
    ('2023-11-01'::date),
    ('2023-12-01'::date)   
)
SELECT *
    ,CURRENT_DATE
    ,ADD_MONTHS(CURRENT_DATE, -4) as date_start
    ,ADD_MONTHS(CURRENT_DATE, -1) as date_end
    ,OTO_START_DATE BETWEEN date_start and date_end as filter_logic
FROM OTO_HISTORICAL_TRACKING_DETAIL
--WHERE OTO_START_DATE BETWEEN ADD_MONTHS(CURRENT_DATE, -4) 
--                         AND ADD_MONTHS(CURRENT_DATE, -1)

Well that could be the problem; current_date is the exact "day" and thus a month earlier, which might not be what you want. Perhaps you want to have the start/end of those months at which point you will want to use DATE_TRUNC('month', current_month) and LAST_DATE

like:

with OTO_HISTORICAL_TRACKING_DETAIL(OTO_START_DATE) as (
    select * from values 
    ('2023-06-01'::date),
    ('2023-07-01'::date),
    ('2023-08-01'::date),
    ('2023-09-01'::date),
    ('2023-10-01'::date),
    ('2023-11-01'::date),
    ('2023-12-01'::date)   
)
SELECT *
    ,CURRENT_DATE
    --,ADD_MONTHS(CURRENT_DATE, -4) as date_start
    --,ADD_MONTHS(CURRENT_DATE, -1) as date_end
    --,OTO_START_DATE BETWEEN date_start and date_end as filter_logic

    ,ADD_MONTHS(date_trunc('month', CURRENT_DATE), -3) as month_start
    ,last_day(CURRENT_DATE, 'month') as month_end
    ,OTO_START_DATE BETWEEN month_start and month_end as filter_logic_2
FROM OTO_HISTORICAL_TRACKING_DETAIL

Giving: