PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Return SQL rows between from_date and to_date regardless of year in SQL only matching day and month not year

I have a table and it has a column start_date. I have two variable fields/filters from_date and to_date on which I want my records to be filtered.

I am working on a SQL/PostgreSQL/pgAdmin4 script that returns all the rows whose start_date is between from_date and to_date only matching day and month (not year). For example: let’s say from_date is ‘2023-12-25’ and to_date is ‘2023-02-07’. I want to get all the rows where the start_date lies between these dates regardless of the year.

So, it will return rows falling between 25th Dec and 7th Feb. Thanks in advance.

I have checked this answer In SQL how to return records matching date and month only (ignore year)

and tried the same with the query:

and tried the following query:

SELECT *
FROM mytable 
WHERE status = 'Active'
AND (
    (
        EXTRACT(MONTH FROM from_date::DATE) <= EXTRACT(MONTH FROM to_date::DATE) 
        AND (TO_CHAR(start_date::date, 'MM/DD') BETWEEN TO_CHAR(from_date::date, 'MM/DD') AND TO_CHAR(to_date::date, 'MM/DD'))
    ) 
    OR (
        EXTRACT(MONTH FROM from_date::DATE) > EXTRACT(MONTH FROM to_date::DATE) 
        AND (
            (TO_CHAR(start_date::date, 'MM/DD') BETWEEN TO_CHAR(from_date::date, 'MM/DD') AND '31/12')
            OR (TO_CHAR(start_date::date, 'MM/DD') BETWEEN '01/01' AND TO_CHAR(to_date::date, 'MM/DD'))
        )
    )
);

but not getting any data

Tags:

2

Answers


  1. 0

    You can try

    select *
from table_name
where
  date_part('doy', start_date) between date_part('doy', from_date) and date_part('doy', to_date)

    This will use the numerical version of the day of the year as the comparison between the given dates.

    You might need to swap from_date and to_date around as I’ve taken to_date to be the later date.

    See https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-date_part/ for more date parts you can use with date_part.

    EDIT: To account for the discrepancies of leap years try this

    select id
from table_name
where
  (
    date_part('month', start_date) < date_part('month', to_date) and
    date_part('month', start_date) > date_part('month', from_date)
  ) or
  (
    date_part('month', from_date) < date_part('month', to_date) and
    date_part('month', start_date) = date_part('month', to_date) and
    date_part('day', to_date) > date_part('day', start_date)
  ) or
  (
    date_part('month', from_date) < date_part('month', to_date) and
    date_part('month', start_date) = date_part('month', from_date) and
    date_part('day', from_date) < date_part('day', start_date)
  ) or
  (
    date_part('month', from_date) = date_part('month', to_date) and
    date_part('month', start_date) = date_part('month', from_date) and
    date_part('day', from_date) < date_part('day', to_date) and
    date_part('day', start_date) between date_part('day', from_date) and date_part('day', to_date)
  )
    Login or Signup to reply.
  2. 0

    I created this, which seems to work:

    with recursive d as (
    select 
        '2023-12-31'::DATE as startdate,
        '2023-12-25'::DATE as fromdate,
        '2023-02-07'::DATE as todate
  ),
fromto as (
select 
   startdate,
   fromdate,
   todate,
   case when startdate>=fromdate then extract(month from fromdate) else extract(month from todate) end as month_from2,
   case when startdate>=fromdate then extract(day from fromdate) else extract(day from todate) end as day_from2,
   case when startdate>=fromdate then extract(month from todate) else extract(month from fromdate) end as month_to2,
   case when startdate>=fromdate then extract(day from todate) else extract(day from fromdate) end as day_to2
from d
),
dates as (
   select '2022-01-01'::DATE as d
   union all
   select (d+INTERVAL '1 day')::DATE from dates where d<'2025-01-01'::date
   )
select * 
from dates
cross join fromto
where case when startdate>=fromdate 
      then (extract(month from d)>month_from2 or (extract(month from d)=month_from2 and extract(day from d)>=day_from2))
            or (extract(month from d)<month_to2 or (extract(month from d)=month_to2 and extract(day from d)<=day_to2))
      else (extract(month from d)>month_from2 or (extract(month from d)=month_from2 and extract(day from d)>=day_from2))
            and (extract(month from d)<month_to2 or (extract(month from d)=month_to2 and extract(day from d)<=day_to2))
     end
;

    see: DBFIDDLE

    • The CTE d contains the values like startdate, fromdate and todate
    • The CTE fromto calculates some variables used in the final statement
    • The CTE dates creates a list of all the dates from ‘2022-01-01’ to ‘2025-01-01’
    • The final SQL statement contains pretty standard SQL stuff, and does not need more explanation (IMHO 😉)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search