I have a table like the following
| name | birthdate |
|---|---|
| John | 1980-10-25 |
| Mike | 1975-09-17 |
| Jill | 1987-01-13 |
| Anne | 1999-02-01 |
Today's date is 2022-02-22.
How would I write a sql query that returns the name and last time their birthday occurred? As in the same day and month from 2022 if their birthday has already happened this year or from 2021 if their birthday hasn't happened this year
Sample response would be
| name | last_birthdate |
|---|---|
| John | 2022-10-25 |
| Mike | 2022-09-17 |
| Jill | 2021-01-13 |
| Anne | 2021-02-01 |
Use date arithmetic:
SELECT name,
birthdate + EXTRACT (year FROM
age(current_timestamp, birthdate)
) AS last_birthday
FROM tab;
You can use a conditional:
select name, case when date(extract(year from now())||regexp_replace(birthdate::text, '^\d+', '')) < date(now()) then extract(year from now())-1||regexp_replace(birthdate::text, '^\d+', '') else extract(year from now())||regexp_replace(birthdate::text, '^\d+', '') end from people
select
name,
case
when
birthdate + concat(extract(YEAR from now()) - extract(YEAR FROM birthdate), ' years')::interval > now()
then
birthdate + concat(extract(YEAR from now()) - extract(YEAR FROM birthdate) - 1, ' years')::interval
else
birthdate + concat(extract(YEAR from now()) - extract(YEAR FROM birthdate), ' years')::interval
end as last_birthdate
from tab