11 Replies - 432 Views - Last Post: 09 March 2018 - 11:48 AM

#1 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:22 AM

I'm trying to display salaries for employees that are or older than 60yrs of age.

I have a column with the birthdate listed "birth_date" format "160-01-20"

I tried doing this in the where clause
CURDATE() - employees.birth_date >= 60



However, it doesn't return the accurate information.

Any help is appreciated.

Thanks,

View PostDanZman, on 09 March 2018 - 10:19 AM, said:

I'm trying to display salaries for employees that are or older than 60yrs of age.

I have a column with the birthdate listed "birth_date" format "1960-01-20"

I tried doing this in the where clause
CURDATE() - employees.birth_date >= 60


UPDATE sorry, in the original post I didn't have the year in the birth_date correctly.
However, it doesn't return the accurate information.

Any help is appreciated.

Thanks,


Is This A Good Question/Topic? 0
  • +

Replies To: Calculate Age in SQL that is >= 60

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:26 AM

Perhaps show the relevant code around that for folk to get a better idea what you are doing.
Was This Post Helpful? 0
  • +
  • -

#3 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:28 AM

This is the full code

use employees;
SELECT 
    employees.first_name, employees.last_name, salaries.salary
FROM
    employees
        JOIN
    salaries ON employees.emp_no = salaries.emp_no
WHERE
    CURDATE() - employees.birth_date >= 60

GROUP BY first_name


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:28 AM

Any particular reason you opted against using a SQL function like 'DATEDIFF'?
Was This Post Helpful? 0
  • +
  • -

#5 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:30 AM

View Postmodi123_1, on 09 March 2018 - 10:28 AM, said:

Any particular reason you opted against using a SQL function like 'DATEDIFF'?


I tried using it but I got an error about an incorrect parameter count in the call.

This is what I had for DATEDIFF
DATEDIFF(yy, CURDATE(), employees.birth_date)


Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:35 AM

I would have to see what you tried and the exact error message. In theory that should work.
Was This Post Helpful? 0
  • +
  • -

#7 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:38 AM

View Postmodi123_1, on 09 March 2018 - 10:35 AM, said:

I would have to see what you tried and the exact error message. In theory that should work.

Below is the code and the error message was "Incorrect parameter count in the call to native function DATEDIFF'
use employees;
SELECT 
    employees.first_name, employees.last_name, salaries.salary
FROM
    employees
        JOIN
    salaries ON employees.emp_no = salaries.emp_no
WHERE
    DATEDIFF(yy, CURDATE(), employees.birth_date) >= 60
GROUP BY first_name


Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:38 AM

Which DB is this? MSSQL, MYSQL, ORACLE, etc?
Was This Post Helpful? 0
  • +
  • -

#9 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:39 AM

View Postmodi123_1, on 09 March 2018 - 10:38 AM, said:

Which DB is this? MSSQL, MYSQL, ORACLE, etc?


MySQL Workbench
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14423
  • View blog
  • Posts: 57,820
  • Joined: 12-June 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:42 AM

Ah.. well the docs indicate:

Quote

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

https://dev.mysql.co...nction_datediff

If you don't want to work with days then alternatively you can peep at the 'year' function.
https://dev.mysql.co...l#function_year
Was This Post Helpful? 0
  • +
  • -

#11 DanZman   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 24-January 18

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 10:46 AM

View Postmodi123_1, on 09 March 2018 - 10:42 AM, said:

Ah.. well the docs indicate:

Quote

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

https://dev.mysql.co...nction_datediff

If you don't want to work with days then alternatively you can peep at the 'year' function.
https://dev.mysql.co...l#function_year

I See thanks for your help!
Was This Post Helpful? 0
  • +
  • -

#12 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3585
  • View blog
  • Posts: 16,403
  • Joined: 20-September 08

Re: Calculate Age in SQL that is >= 60

Posted 09 March 2018 - 11:48 AM

Try
SELECT first_name, last_name, birth_date FROM employees WHERE birth_date <= date_add(curdate(), INTERVAL -60 YEAR);

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1