Compare date filed with month and year in Postgres-CodePudding

I have a date field in one of my tables and the column name is from_dt. Now I have to compare a month and year combination against this from_dt field and check whether the month has already passed. The current database function uses separate conditions for the month and the year, but this is wrong as it will compare month and year separately. The current code is like this

SELECT bill_rate, currency FROM table_name WHERE 
emp_id = employee_id_param
AND EXTRACT(MONTH FROM from_dt) <= month_param
AND EXTRACT(YEAR FROM from_dt) <= year_param

Now the fromt_dt field has value 2021-10-11. If I give month_param as 01 and year_param as 2022, this condition will not work as the month 10 is greater than 1, which I have given. Basically, I need to check whether 01-2022 (Jan 2022) is greater than r equal to 2021-10-01(October 1st, 2021). It would be very much helpful if someone can shed some light here.

CodePudding user response：

If you just want to check whether one date is >= then another:

# select '2022-01-01'::date >= '2021-10-11'::date;
 ?column? 
----------
 t

If you want to restrict to year/month then:

select date_trunc('month','2022-01-01'::date) >= date_trunc('month', '2021-10-11'::date);
 ?column? 
----------
 t

Where the date_trunc components are:

select date_trunc('month','2022-01-01'::date) ;
  date_trunc       
------------------------
 2022-01-01 00:00:00-08

 select date_trunc('month','2021-10-11'::date) ;
       date_trunc       
------------------------
 2021-10-01 00:00:00-07

See Postgres date_trunc for more information.

CodePudding user response：

Assuming the given year_param and month_param are integers you can use the make_date function to create the first of the year_month and date_trunc to get the first on the month from the table. Just compare those values. (See date functions) So:

select bill_rate, currency 
  from table_name  
 where emp_id = employee_id_param 
   and date_trunc('month',from_dt) = 
       make_date( year_param, month_param, 01);