HELP... Data Calculation

[StacyStacy]

How do I get those employees who were 60 years old at date of hire? the date format is: 2002-10-16 00:00:00.000

Here's that I have and it is returning everyone:

SELECT
hire_date
,birth_date
,RTRIM(last_name) AS LastName
,RTRIM(first_name)AS FirstName
,empno
FROM empper

WHERE --("empper"."birth_date") -("empper"."hire_date") < '1951'
hire_date - birth_date >= 60
ORDER BY last_name

 

[PWise]

Datediff (y,hire_date , birth_date) >= 60

 

[StacyStacy]

It's not returning data.

SELECT
birth_date
,hire_date
,RTRIM(last_name) AS LastName
,RTRIM(first_name)AS FirstName
,empno
FROM empper

WHERE Datediff (y,hire_date , birth_date) >= 60
--("empper"."birth_date") -("empper"."hire_date") < '1951'
ORDER BY last_name

 

[StacyStacy]

Sorry about that.

It returns data if I use <= 60. So this tells me that my code is still incorrect. In the return, I see someone who is 29 years old. This person should not be listed in the return. I only want those who are 60 years old or older on their hiredate based off of their birthday. Where am I going wrong?


SELECT
birth_date
,hire_date
,RTRIM(last_name) AS LastName
,RTRIM(first_name)AS FirstName
,empno
FROM empper

WHERE Datediff (y,hire_date , birth_date) <= 60
ORDER BY last_name

 

[StacyStacy]

Please help someone, it's pretty urgent ...

 

[gmmastros]

[tt] WHERE Datediff (y , birth_date,hire_date) > 60[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

Sorry sb
WHERE Datediff (yy,birth_date , hire_date) >=60

 

[StacyStacy]

Thanks so much!

I'm an intermediate coder ...
Am I understanding the logic correctly?

Select everything
WHERE the difference between two dates, looking at the last 2 digit year of (birth_date) then (hire_date) is => 60?

 

[PWise]

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates

Italics Mine

 

[drlex]

Note George's answer is greater than 60, otherwise you'll be including some 59 year olds in your list.

soi là, soi carré

 

[StacyStacy]

Right, because of the operator: >=, which means 'greater than or equal to'.

Thanks!

 

[drlex]

Thinking more on this, you might be better to consider the days between birth date and hire date, rather than the crossing of the year boundary:

select datediff(dd, birth_date, hire_date)/365.25 >= 60

Consider the situation of
birth date 1950-12-31 and hired 2010-01-01 = 60 years on datediff of years
and
birth date 1950-01-01 and hired 2010-12-31 = 60 years on datediff of years
Only the second is 60 at the time of hiring.





soi là, soi carré

 

[StacyStacy]

Thanks!

 

[StacyStacy]

How do you write the code to compare to the entire date ... month,day and year? This didn't work for me ...

WHERE Datediff (YYYYMMDD,birth_date , hire_date) >=60

thanks,
stacy

 

[drlex]

Alas, one can't; the DATEDIFF function takes only one measurement of time - from Years downwards. As PWise commented and as per my example above, using year counts the number of cross-overs from one calendar year to the next, such that DATEDIFF(yy,'2011-01-01','2012-12-31') is still the same as DATEDIFF(yy,'2011-12-31','2012-01-01').
Note that when SQL server is set to English, Month is MM or M and Minute is MI or N.

An alternative tack to take, which might be easier to comprehend, is to use DATEADD on the birth_date. It uses the same time parameters as DATEDIFF.
Add 60 years to the birth_date and then compare to the hire date to see whether it precedes.

WHERE DATEADD(YY,60,birth_date) <= hire_date

HTH


soi là, soi carré

 

[StacyStacy]

Thanks! It worked!