2

I have a table with names and birthday year of doctor. On index page i need to show only doctors who is undex 40 years old. I need to subtract birthday year from current year and show only result under 40 ... 

SELECT * FROM `doctor` WHERE dateadd(year, `date_birthday`, getdate())

i try to cast the date sa datetime to date as year, but everytime it is return NULL

SELECT id,CAST((CURDATE()-date_birthday) AS DATE) AS difference FROM doctor
allen
  • 370
  • 2
  • 14
  • 2
    Tag your question with the database you are using. – Gordon Linoff May 25 '18 at 12:18
  • Possible duplicate of [Get difference in years between two dates in MySQL as an integer](https://stackoverflow.com/questions/10506731/get-difference-in-years-between-two-dates-in-mysql-as-an-integer) – Razvan Dumitru May 25 '18 at 12:26
  • 1
    SELECT id,(CURDATE()-date_birthday) AS difference FROM doctor this is work, but it is convert it to timestamp ... result is 20178533 ... – allen May 25 '18 at 12:45

2 Answers2

2

You don't specify a database and your formatting is inconsistent. The ANSI standard syntax would be:

SELECT d.*
FROM doctor d
WHERE date_birthdate >= CURRENT_DATE - INTERVAL '40 YEAR';

Date/time functions differ by database, so this may not work in your particular database. Hence, the need for a database tag.

For instance, in MySQL, you would drop the single quotes:

SELECT d.*
FROM doctor d
WHERE date_birthdate >= CURRENT_DATE - INTERVAL 40 YEAR;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
SELECT * 
FROM `doctor` 
WHERE curdate() > `date_birthday` + interval 40 year
juergen d
  • 201,996
  • 37
  • 293
  • 362