How to convert this SQL-Statement to PostgreSQL?

Question

I have a MySQL Statement which won't run on a PostgreSQL Server.

Here is the original MySQL Query:

SELECT count(*) AS howmany 
FROM members 
WHERE member_status =1 
AND member_sex = 'male' 
AND (YEAR( '2015-12-31' ) - YEAR( member_birthdate ) ) - 
    ( RIGHT( '2015-12-31', 5 ) < RIGHT( member_birthdate, 5 ) ) 
BETWEEN 27 AND 40;

This is my approach:

SELECT COUNT(*) AS howmany FROM members
WHERE member_status =1 
AND member_sex ='male'
AND (EXTRACT(YEAR FROM '2015-12-31'::date) - EXTRACT(YEAR FROM member_birthdate)) 
BETWEEN 27 AND 40;

The Goal is, that i want to know how many Members are between 27 and 40 Years old on the qualifying date 2015-12-31. I don't know how to convert the RIGHT Part of the Query.

What error are you getting? Or post up some sample data we can use — Matt
– Matt, Commented Dec 11, 2015 at 9:47

Lukasz Szozda · Accepted Answer · 2015-12-11 09:50:52Z

5

You can use AGE function:

SELECT COUNT(*) AS howmany 
FROM members
WHERE member_status =1 
  AND member_sex ='male'
  AND extract(year from age(timestamp '2015-12-31', member_birthdate))
      BETWEEN 27 AND 40;

edited Dec 11, 2015 at 9:50

answered Dec 11, 2015 at 9:48

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

Beat me too it :) good answer and good use of the age function!

Collectives™ on Stack Overflow

How to convert this SQL-Statement to PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related