DATEADD FUNCTION 2

[graphix03]

Hi...

I'm trying to write a sql statement
that would include people whose status
is active, or status is null or status
= '' or status is terminated but terminated
within the last 30 days. Here is my sql code:

select ....
from .....
where termdate IS NULL OR termdate = ''
OR termdate <= DATEADD(d, 30, GETDATE())

it gave me no error, but what i got in return
it included people terminated more than a
year ago too. how come?

please help, any input would be greatly appreciated.
thanks so much.

 

[checkai]

select .... 
from .....
where ISNULL(termdate,'')=''
OR termdate >= DATEADD(d, -30, GETDATE())

will this work?

 

[nicatt]

Doesn't this say

termdate <= DATEADD(d, 30, GETDATE())
or in other words
termdate less than or equal to 30 days from today?

So if this is July 20, 2004 you should get everyone with a termdate less than August 19, 2004.

Try
termdate >= DATEADD(d, -30, GETDATE())
or in other words
termdate greater than 30 days ago

 

[graphix03]

THANKS Checkai, I like your code better . i tried it,
it still pulls up 2003 data of more than 30 days, so I think it's somewhere in my join clause, not the where clause, so i'm working on that part now. but thanks so much for your input.

 

[graphix03]

thanks nicatt, yeah, i think that what checkai
corrected me for. thanks for your input. you're right.

 

[graphix03]

i GOT IT NOW, IT WAS THE OPERATOR, I HAD
THE WRONG WAY, so the following code
should give the termdate within the last
30 days.

(TERMDATE >= DATEADD(d, -30, GETDATE()))

I'M SO HAPPY!

 

[nicatt]

Yes, I noticed that checkai and I had posted almost at the same time so that neither of us knew the other was posting.

Glad it worked out for you.