RE: transact-sql datediff function
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-1299WWW: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
Re: transact-sql datediff function
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 designedBasically 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
RE: transact-sql datediff function
Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
Re: transact-sql datediff function
thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
RE: transact-sql datediff function
I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ 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
Re: transact-sql datediff function
well, here... i'm a newbie to transact-sql and i have my nose in this book, guru's guide to transact-sql. don't laugh...that's just like me to start with the headscratcher and fill in the gaps as i go. :) anyway, the book goes thru code for possible solutions to the classic problem of finding all employees whose hire date anniversaries fall within the next 30 days. they go on to say that there are a number of 'false' solutions, one of which is filtering the resultset to include only records where the months are the same for both the hire date and today's date. this solution isn't good because is doesn't allow for the fact that the 30 days may span over one, two, or even three months. the other 'false' solution does all this casting and converting to the hire date (which i don't even pretend to understand yet) then checks to see if the result is between today's date and today's date plus 30 (days) by using the getdate() function. but this solution doesn't factor in the leap year exception. now here's what the author says is the 'best' solution but i can't get it to work as expected: select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) datediff (yy, hire_date, getdate()) maybe i'm doing something stupid. does this code work for you? ~ dina - Original Message - From: Ryan Pieszak [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 3:31 PM Subject: RE: transact-sql datediff function I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
RE: transact-sql datediff function
Dina... My guess would be that it's returning an integer and rounding it up... Try doing: (datediff(mm, hire_date, getdate())) / 12 This should return a floating number. Then floor it if you want it to always round down as so: Floor((datediff(mm, hire_date, getdate())) / 12) -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 1:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
RE: transact-sql datediff function
Somebody back me up on this, but I THINK the book has a typo. I THINK it should be this: select fname, lname, hire_date from employees where datediff(dd,hire_date,getdate()) + 30) datediff(dd,hire_date,getdate()) I did some real quick testing, and if you change the yy (years) to dd (days), it works. Let me know if it works for you. I'm leaving for the day, I'll check my mail in the morning. Ryan -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 5:16 PM To: CF-Talk Subject: Re: transact-sql datediff function well, here... i'm a newbie to transact-sql and i have my nose in this book, guru's guide to transact-sql. don't laugh...that's just like me to start with the headscratcher and fill in the gaps as i go. :) anyway, the book goes thru code for possible solutions to the classic problem of finding all employees whose hire date anniversaries fall within the next 30 days. they go on to say that there are a number of 'false' solutions, one of which is filtering the resultset to include only records where the months are the same for both the hire date and today's date. this solution isn't good because is doesn't allow for the fact that the 30 days may span over one, two, or even three months. the other 'false' solution does all this casting and converting to the hire date (which i don't even pretend to understand yet) then checks to see if the result is between today's date and today's date plus 30 (days) by using the getdate() function. but this solution doesn't factor in the leap year exception. now here's what the author says is the 'best' solution but i can't get it to work as expected: select fname, lname, hire_date from employees where datediff (yy, hire_date, getdate()) + 30) datediff (yy, hire_date, getdate()) maybe i'm doing something stupid. does this code work for you? ~ dina - Original Message - From: Ryan Pieszak [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 3:31 PM Subject: RE: transact-sql datediff function I think the only way to check for that a year has passed is to use the day, and determine if it's greater than 365. If DateDiff(dd,myDate,Current_Timestamp) 365 Begin . End -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 4:17 PM To: CF-Talk Subject: Re: transact-sql datediff function thanks for taking a look at it, margaret. but the whole idea is that i'm trying to find the difference (in years) between the hire date and today's date. ~ dina - Original Message - From: Margaret Fisk [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, May 06, 2002 2:56 PM Subject: RE: transact-sql datediff function Just a guess, but in the datediff you are just extracting the year. If you want to include the month in the calculation, you should probably get the month part too. Margaret -Original Message- From: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 11:20 AM 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 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
