I hate to say it but the function is working as designed. I ran into the same issue when looking for the number of months between dates.
>From SQL Server's Books Online: 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. 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. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Dina Hess [mailto:[EMAIL PROTECTED]] > Sent: Monday, May 06, 2002 2:20 PM > To: CF-Talk > Subject: OT: transact-sql datediff function > > > hi all, > > i posted this on the sql list but got no response. > > i have a test table named employees with the following fields: > > fname varchar > lname varchar > hire_date datetime > > the record i'm querying contains 2001-05-11 00:00:00.000 in the > hire_date field. but this query returns 1 rather than the > expected 0: > > select datediff(yy, hire_date, getdate()) as yearsdiff from > employees where fname = 'ann' > > this should give me the number of years between 5/11/2001 and > today's date of 5/6/2002, which should be 0 since it's not 5/11 > yet. so why am i getting 1??? > > ~ dina > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
