SQL calculations issue

Question

I have following task:

Show the list of first, last names and ages of the employees whose age is greater than 55. The result should be sorted by last name.

This is my code:

SELECT 
    LastName, FirstName,  
    (CASE 
        WHEN (CONVERT(INT, GETDATE()) - CONVERT(INT, BirthDate)) > 55 
           THEN CONVERT(INT, GETDATE()) - CONVERT(INT, BirthDate) 
     END) as Age 
FROM 
    Employees  
ORDER BY 
    LastName

This is what I get:

This is the BirthDate table (datetime):

Why does the age go so crazy? What's wrong?

Because you are converting a `DATE` to `INT` and displaying the `INT` version of the date results? — CodeLikeBeaker, Dec 30 '16 at 16:45
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Dec 30 '16 at 16:50
Which DBMS are you using? "SQL" is a just query language, not a DBMS product — , Dec 30 '16 at 16:50

score 1 · Answer 1 · edited Dec 30 '16 at 16:50

For age, use expression

 DateDiff(year, birthdate, getdate()) +
    case when Month(getdate()) > Month(birthdate) 
           or Day(getdate()) >= Day(birthdate) 
       then 1 else 0 end

i.e.,

SELECT LastName, FirstName,  
    DateDiff(year, birthdate, getdate()) +
    case when Month(getdate()) > Month(birthdate) 
           or Day(getdate()) >= Day(birthdate) 
       then 1 else 0 end Age 
FROM Employees  
where DateDiff(year, birthdate, getdate()) +
    case when Month(getdate()) > Month(birthdate) 
           or Day(getdate()) >= Day(birthdate) 
       then 1 else 0 end > 55
ORDER BY LastName

score 0 · Accepted Answer · edited Dec 30 '16 at 18:16

0

 SELECT * 
 FROM
     (SELECT 
          LastName, FirstName,  
          (CASE 
              WHEN DATEDIFF(yy, BirthDate, GETDATE()) > 55 
                 THEN DATEDIFF(yy, BirthDate, GETDATE()) 
                 ELSE 0 
            END) AS Age 
      FROM 
          Employees) AS ABC
WHERE 
    ABC.Age != 0
ORDER BY 
    ABC.LastName

edited Dec 30 '16 at 18:16

marc_s

732,580
175
1,330
1,459

answered Dec 30 '16 at 16:45

LONG

4,490
2
17
35

Try to use `DATEDIFF` function instead what you tried to get the age difference – LONG Dec 30 '16 at 16:46
datediff only counts the number of date boundaries crossed to get from one date to another. It does not calculate difference. `DateDiff(yy, '31Dec2016', '1Jan2017')` and `DateDiff(yy, '1Jan2016', '31Dec2017')` will both return One (1). – Charles Bretana Dec 30 '16 at 16:51
two days old (`31 dec 2016` to `1 Jan 2017`), is hardly age 1. although `1 jan 2016 to 31 Dec 2017` is, technically, age 1, it is 729 days old, one day shy of age 2. – Charles Bretana Dec 30 '16 at 16:58
OH! I see, the 2nd case make more sense than the 1st one. Thanks for correcting me such a mistake I have not noticed for long time. – LONG Dec 30 '16 at 17:03
Until corrected, this answer is WRONG. it will give incorrect age for cases where current date in a year is on or after the individuals Birthday. i.e, if Birthday is 1 June, this will give correct answer every year from 1 Jan up until and including 30 may, but will continue to give the same (INCORRECT) answer from 1 June through 31 December. – Charles Bretana Dec 30 '16 at 18:54

score 0 · Answer 3 · answered Dec 30 '16 at 16:47

0

Problem in your result is you are converting Date to int and you didn't convert the Int back to Date

Add the filter in Where clause. To calculate age I always use below method

SELECT FLOOR(DATEDIFF(DAY, '1990-07-20', getdate()) / 365.25)

Result : 26

SELECT LastName,
       FirstName,
       Floor(Datediff(DAY, BirthDate, Getdate()) / 365.25) AS Age
FROM   Employees
WHERE  Floor(Datediff(DAY, BirthDate, Getdate()) / 365.25) > 55
ORDER  BY LastName

answered Dec 30 '16 at 16:47

Pரதீப்

91,748
19
131
172

Why introduce this minor inaccuracy of 0.25 when you can just use year as the parameter in the DATEDIFF function? – K Richard Dec 30 '16 at 16:59
@KRichard - using `year` part is not going to get you exact Age – Pரதீப் Dec 30 '16 at 17:00

score 0 · Answer 4 · edited May 23 '17 at 12:33

0

I strongly advise you to not use datediff(). It does not work intuitively. It counts the number of year boundaries between two dates. Although there is a relationship between New Year's celebrations and age, the two are not equivalent.

Instead:

SELECT LastName, FirstName
FROM Employees  
WHERE birthdate < DATEADD(year, -55, getdate())
ORDER BY LastName;

Unfortunately, that doesn't get the age. That is a bit tricky. You could use datediff() just to be "close enough". The exactly logic is a bit of a pain. I would refer you here.

edited May 23 '17 at 12:33

Community

1
1

answered Dec 30 '16 at 16:58

Gordon Linoff

1,242,037
58
646
786

This answer has a problem and that's (as you pointed out yourself), is that the query is not returning the age, but in my opinion it has better performance because of the Where clause (less row level calculations) – Sparrow Dec 30 '16 at 17:10
@FeryalBadili . . . Unfortunately, the accepted answer doesn't correctly calculate age. – Gordon Linoff Jan 02 '17 at 01:33

SQL calculations issue

4 Answers4