to summarize, the following transact-sql code is touted by "The Guru's Guide to Transact-SQL" as the best solution to return only those employees whose hire date anniversaries fall within the next 30 days:
select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) > datediff (yy, hire_date, getdate()) but it doesn't work. here are some of your comments about why it doesn't work: >> Just a guess, but in the datediff you are just extracting the year. we're not really 'extracting' the year; we're trying to return the difference in dateparts (years) between two dates, the startdate and the enddate. >> If you want to include the month in the calculation, you should probably get the month part too. well you can only enter one datepart. besides, it's implied from the above code that datediff is supposed to compare the entire date and just return the difference of the indicated datepart. but i see where you're going with this and, truth is, when the year datepart is used with this function, it *acts like* it's not evaluating on the entire date. >> Somebody back me up on this, but I THINK the book has a typo. .. I did some real quick testing, and if you change the yy (years) to dd (days), it works. thanks for taking a look at this so late in the workday, but you might want to test that again. by returning the difference in days, the left side of the expression will always be greater than the right side, so *all* employees are returned. :) probably not a typo; the code correlates to the author's logic, which is clearly spelled out: "If the number of years between the hire date and today's date plus 30 days exceeds the number of years between the hire date and today's date, a hire date anniversary must have occurred within those thirty days, regardless of the actual date." >> My guess would be that it's returning an integer and rounding it up... did you mean it's rounding the result to the nearest integer? actually, that occurred to me; but i didn't understand how your code would prove it. besides, i doubt if it's a rounding issue because the datediff function is supposed to increment the count as it crosses datepart boundaries, according to msdn library's transact sql reference: "The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1." >> I hate to say it but the function is working as designed....Basically what you will find is that the number of years difference between the 2 dates is the count of how many times you go past January 1. Each time you pass Jan 1 it adds 1 year. yes. i realized the entire date was *not* being considered by the datediff function to establish a boundary when i tested using 12/31/2001 as a startdate and 1/01/2002 as an end date. the result was 1 for both the yy and mm dateparts. so i would have to conclude that this piece of code is, conceptually, in error. incredible! maybe *i* should write a book. it seems you can say just about anything, and get paid to do it. :) ~ dina ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
