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

Reply via email to