CODEWITHSUNDEEP
sqlpostgresql
Sid W.
Sid W.

Reputation: 97

How to get the last occurrence of a date?

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

Upvotes: 1

Views: 201

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246083

Use date arithmetic:

SELECT name,
       birthdate + EXTRACT (year FROM
                            age(current_timestamp, birthdate)
                           ) AS last_birthday
FROM tab;

Upvotes: 3

Marko
Marko

Reputation: 988

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

Upvotes: 0

Ajax1234
Ajax1234

Reputation: 71451

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

Upvotes: 0

Related Questions