I have a date column that contains people's birthdays. Some of them contain dates that are over 90 years ago and I want those to be capped at 90 years ago.

Example

current      desired
1930-01-01   1932-01-01
1930-02-11   1932-02-11

As you can see the desired state is to have the birthdate be 90 years from current date if it is naturally over 90 years ago.

CodePudding user response：

It was sure tricky one. I even checked date diff for 90 years max at timeanddate.com - looks ok. Do appreciate feedback if anyone finds issue in this.

select iff(extract(year,dateadd('year',90,column1::date))>extract(year,'2022-02-28'::date),
    column1,
    dateadd(day,
    datediff(day,column1::date,dateadd(year,-90,'2022-02-28'::date)),
    column1::date
    )) as test
    from
    values
    ('1932-02-29'::date)
    ,('1932-02-28'::date)
    ,('1932-03-01'::date)
    ,('1928-02-29'::date)
    ,('1924-02-29'::date)
    ,('1930-01-01'::date)
    ,('1940-05-01'::date)
    ,('1950-12-01'::date)
    ,('1980-10-01'::date)
    ,('1920-06-01'::date)
    ;
    
 ------------ 
| TEST       |
|------------|
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1932-02-28 |
| 1940-05-01 |
| 1950-12-01 |
| 1980-10-01 |
| 1932-02-28 |
 ------------ 

CodePudding user response：

it's more tricky than I initially guessed, and it can be written more simpler, now that there seems "be competition", albeit, this is less readable, than it was"

The no date over 90 years version:

SELECT column1 as "current"
    ,iff(column1 < dateadd('year',-90, CURRENT_DATE), 
         date_from_parts(
            year(CURRENT_DATE) - 90   (month(column1)*10 day(column1) < month(CURRENT_DATE)*10   day(CURRENT_DATE))::int, 
             month(column1), 
             day(column1)), 
         column1
        ) as desired
FROM VALUES 
    ('1934-01-01'::date),
    ('1932-03-28'::date),
    ('1932-03-20'::date),
    ('1931-03-28'::date),
    ('1931-03-20'::date),
    ('1930-01-01'::date),
    ('1930-02-11'::date);

The no year prior to the 90 years ago (aka the title) version:

SELECT column1 as "current"
    ,iff(year(column1) < year(CURRENT_DATE)-90, date_from_parts(year(CURRENT_DATE)-90, month(column1), day(column1)), column1) as desired
FROM VALUES
    ('1934-01-01'::date),
    ('1932-03-28'::date),
    ('1932-03-20'::date),
    ('1931-03-28'::date),
    ('1931-03-20'::date),
    ('1930-01-01'::date),
    ('1930-02-11'::date)
;