CODEWITHSUNDEEP
sqlpostgresqldatetime
Macbernie
Macbernie

Reputation: 1323

Postgresql date subtract

How can I make a valid request like:

UPDATE b2c SET tranche = '18 - 25'
WHERE (dateofbirth::date BETWEEN NOW()::date - 18 'year' AND NOW()::date - 25 'year')

Thanks for help

Upvotes: 0

Views: 1685

Answers (3)

klin
klin

Reputation: 121889

You can use Postgres cast syntax:

UPDATE b2c SET tranche = '18 - 25'
WHERE dateofbirth::date BETWEEN 
    NOW()::date - '25y'::interval AND 
    NOW()::date - '18y'::interval

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21963

By using EXTRACT() and Age()

select EXTRACT(year FROM age('1995-08-04'::date))::int age

Output:

age
integer
--------
      20

So you can write where condition like below

UPDATE b2c SET tranche = '18 - 25'
WHERE EXTRACT(year FROM age(dateofbirth))::int >=18 
 AND  EXTRACT(year FROM age(dateofbirth))::int <=25

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125504

dateofbirth::date BETWEEN 
    (NOW() - interval '25 year')::date AND 
    (NOW() - interval '18 year')::date

Upvotes: 1

Related Questions