StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Those records <em>should</em> be missing because the number of years between stDob and stJoinDt is <em>not</em> between 18 and 75, as you are filtering them out with your condition that stDob and stJoinDt differ by between 18 and 75 years:</p> <pre><code>with student as ( select 'Bob' as stName, 1 as stId, '10/08/1925' as stDob, '2011-01-03' as stJoinDt union select 'Bob' as stName, 2 as stId, '04/18/1935', '2011-01-19' union select 'Bob' as stName, 3 as stId, '12/11/1928', '2011-06-06' union select 'Bob' as stName, 4 as stId, '1/24/1927 ', '2011-04-18' union select 'Bob' as stName, 5 as stId, '04/18/1918', '2011-04-20' ) SELECT stName ,stId ,stDob --(varchar(15)) ,stJoinDt --(datetime) ,datediff(yy, stu.stDob, stu.stJoinDt) as DiffYears FROM student stu </code></pre> <p>Output:</p> <pre><code>stName stId stDob stJoinDt DiffYears Bob 1 10/08/1925 2011-01-03 *86* (>75) Bob 2 04/18/1935 2011-01-19 *76* (>75) Bob 3 12/11/1928 2011-06-06 *83* (>75) Bob 4 1/24/1927 2011-04-18 *84* (>75) Bob 5 04/18/1918 2011-04-20 *93* (>75) </code></pre> <p>My guess would be you were wanting to capture all records where the person is at least 18 years old. In that case, remove the 75 part from the filter:</p> <pre><code>WHERE DATEDIFF(yy,stu.stDob,stu.stJoinDt) >= 18 -- STOP HERE </code></pre> <p>Although technically this does not perform the correct calculation, because it is only finding the difference in the year values and not taking into account day and month. For instance, a date-of-birth of 12/31/1990 and a join date of 1/1/2008 would register as 18 years even though the person is only 17 years, 1 day old. I would recommend instead using the solution provided in <a href="https://stackoverflow.com/questions/283764/finding-someones-age-in-sql">this question</a>:</p> <pre><code>where (DATEDIFF(YY, stu.stDob, stu.stJoinDt) - CASE WHEN( (MONTH(stDob)*100 + DAY(stDob)) > (MONTH(stJoinDt)*100 + DAY(stJoinDt)) ) THEN 1 ELSE 0 END ) >= 18 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload