-1

I have a table that contains birthday date in 3 columns, as bday_day, bday_month and bday_year, With birth_year nullable.

I have to write a query to find the upcoming next month birthdays, Even if the birth_year is null. (With December to January cases to be taken care)

I have tried the answer given below but got No luck as it is calculated based on year

https://stackoverflow.com/a/18748008/4694871

Edit: I have updated the question from birthdays next month to birthdays in the next 30 days.

Update: Here is what I have tried( with the December to January cases ), Suggestions are welcome..


SELECT to_date(CONCAT(date_part('year',current_date), '-', bday_month, '-',bday_day), 'YYYY-MM-DD') AS birthdate

FROM  users u2 
WHERE (date(to_date(CONCAT(date_part('year',current_date), '-', bday_month, '-',bday_day), 'YYYY-MM-DD')
+ 
interval '1 year' * CASE when(DATE_PART('doy',to_date(CONCAT(date_part('year',current_date), '-', bday_month, '-',bday_day), 'YYYY-MM-DD')) 
< DATE_PART('doy',current_date)) then 1 else 0 end))
            BETWEEN current_date AND date(current_date + INTERVAL '30 day')
Abhishek saharn
  • 927
  • 9
  • 23

4 Answers4

1

Birthdays in the next 30 days:

SELECT * FROM yourt_able
WHERE to_date(lpad(bday_day::text,2,'0')||
              lpad(bday_month::text,2,'0')||
              EXTRACT(YEAR FROM CURRENT_DATE),'DDMMYYYY') <= CURRENT_DATE+30
ORDER BY bday_day;

Demo: db<>fiddle

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Hi Jim, thanks for the answer...can you suggest how can I do it for the next 30 days? – Abhishek saharn Jun 24 '21 at 10:36
  • @Abhisheksaharn check my last edit (demo 2) – Jim Jones Jun 24 '21 at 10:46
  • Tried it. but I guess this will fail for people with birthdays in Jan? – Abhishek saharn Jun 24 '21 at 11:21
  • @Abhisheksaharn I don't get it. It returns records containing dates within the next 30 days. Have you tested it? – Jim Jones Jun 24 '21 at 11:39
  • Abhishek saharn is right, this will fail for birthdays in January, because in December you will have to look for birthdays in January next year, while you compare with January this year and thus dismiss these dates. – Thorsten Kettner Jun 28 '21 at 07:21
  • @ThorstenKettner you're totally right, thanks for pointing out. +1 I was hopping to get this answer from the OP. Sometimes we hear from users "will your solution work?" without even trying it out :-D so yes, your solution looks better than mine! – Jim Jones Jun 28 '21 at 08:53
1

The joy of adequate DATE columns (if you don't have them: convert into dates first):

\i tmp.sql

WITH j (bday_day, bday_month,bday_year) AS (
  VALUES (25,07,NULL),(25,08,NULL),(03,07,2001)
)
, fudge AS (
SELECT *
        , make_date(coalesce(j.bday_year,2000) , bday_month, bday_day) AS zdate
        , make_date(2021 , bday_month, bday_day) AS this_years_birthday
        , now()::date as today
FROM j
        )
select * from fudge
-- WHERE this_years_birthday BETWEEN today AND today+ '30 days':: interval

WHERE this_years_birthday BETWEEN today AND today+ '1 mon':: interval  ;

Result:

 bday_day | bday_month | bday_year |   zdate    | this_years_birthday |   today    
----------+------------+-----------+------------+---------------------+------------
        3 |          7 |      2001 | 2001-07-03 | 2021-07-03          | 2021-06-24
(1 row)

Note: this will fail in December. (left as an exercise for the reader ...)

wildplasser
  • 43,142
  • 8
  • 66
  • 109
1

You can look at it like this: A person's next birthday is either this year or next year. Use the two years hence to build the date.

with this_year_and_next_year(name, birthday) as
(
  select name, make_date(extract(year from current_date)::int, bday_month, bday_day) from users
  union all
  select name, make_date(extract(year from current_date)::int + 1, bday_month, bday_day) from users
)
select name, birthday
from this_year_and_next_year
where birthday between current_date and current_date + interval '30 days'
order by birthday;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

You can just extract next month from current date then compare it with bday_month column. Year is not necessary. Then to find the exact date you can use make_date()

<!-- -->
 create table birthdates (bday_day int , bday_month int,  bday_year int);
 insert into birthdates values(25,6,2021);
 insert into birthdates values(30,6,null);
 insert into birthdates values(29,7,2021);
 insert into birthdates values(2,7,null);
 insert into birthdates values(24,7,2000);
 insert into birthdates values(4,8,null);

Query#1 (find the birthdates within next month)

 select *,
 make_date(cast(extract(year from CURRENT_DATE)as int),bday_month,bday_day)birthdate
 from birthdates
 where (case cast(extract (month from current_date) as int) when 12 then
 1 else cast(extract (month from current_date) as int)+1 end)=bday_month

Output:

bday_day bday_month bday_year birthdate
29 7 2021 2021-07-29
2 7 null 2021-07-02
24 7 2000 2021-07-24

Query#2 (find the birthdates within the next 30 days)

 select *,
 make_date(cast(extract(year from CURRENT_DATE)as int),bday_month,bday_day)birthdate
 from birthdates
 where make_date(cast(extract(year from CURRENT_DATE)as int),bday_month,bday_day)>current_date
 and make_date(cast(extract(year from CURRENT_DATE)as int),bday_month,bday_day)<=current_date + INTERVAL '30 day';

Output:

bday_day bday_month bday_year birthdate
25 6 2021 2021-06-25
30 6 null 2021-06-30
2 7 null 2021-07-02
24 7 2000 2021-07-24

db<>fiddle here

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26