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

Reply via email to