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
