CODEWITHSUNDEEP
sqloracle11g
Jason
Jason

Reputation: 3

SQL Query on find individuals that age is over 60 as of a specific date

Im new to stack so please go easy on me. Ive looked all over the web and cant find anything that really helps me.

So I need to provide details of all regular academics working in the Computing Department who were over 60 years old as of 31/12/2014.

my trouble comes with how would I approach showing data of someone 60+ could you minus one date from another date? or is there is possible sql command that I am missing.

my attempt:

SELECT *
FROM staff, department 
WHERE DOB <= '31/12/1964'
AND staff.department_ID = department.department _ID

Upvotes: 0

Views: 2371

Answers (3)

Guffa
Guffa

Reputation: 700422

There are functions to calculate the difference between dates, but the most efficient is to first calculate the date that a person would be born to be 60 at 2014-12-31. That way you make a direct comparison to a value, so the database can make use of an index if there is one.

Example for Oracle:

select
  PersonId, FirstName, LastName
from
  Person
where
  Born <= add_months(date '2014-12-31', -60 * 12)

Example for MySQL (eventhough you removed the MySQL tag):

select
  PersonId, FirstName, LastName
from
  Person
where
  Born <= date_sub('2014-12-31' 60 year)

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

The oracle add_months function will help you.

where yourfield < add_months(date '1964-12-31', 60*12 )

Upvotes: 0

cool coder
cool coder

Reputation: 19

I think In SQL SERVER

Select Datediff(DAYS,'05-19-2015','05-21-2015')

In My SQL SELECT TIMESTAMPDIFF(HOUR, start_time, end_time) as difference FROM timeattendance WHERE timeattendance_id = '1484'

Upvotes: 0

Related Questions