Help with date function 1

[smatthews]

I have a paid thru date and the month and day is always 9/30. I need a query that shows everyone with a paid thru date going back 5 years. I was trying to avoid using 'between' so I wouldn't need to change it every year. Any suggesions?

Thanks in advance for your help.

 

[mrdenny]

Use between, and dateadd to get a dynamic range that changes as time passes.

select *
from table
where paid_date between dateadd(yy, -5, getdatE()) and getdate()

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Kris11]

Is this what you are looking for?

Select * from myTable
WHERE paid_date BETWEN DATEADD(Year, -5, @passedInDate) AND @passedInDate

where @passedInDate is the date you will be passing in like '09/30/2004' etc.

-Kris

 

[Kris11]

Denny,

You beat me by seconds

-Kris

 

[mrdenny]

lol



Denny

--Anything is possible. All it takes is a little research. (Me)

 

[smatthews]

Thanks for all your help... works like a charm as usual!

 

[ESquared]

Is it a literal 5 years down to the millisecond? Or is it five years based on a cutoff date each year?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[thendrickson]

Good point e-squared. A long time back I was "Losing data" when I took the 90 day requirement literally. Big time "oops

 

[smatthews]

Sorry for the delayed reply... gone for the weekend. It is just anniversary year. But, I would be interested in seeing how you would handle it if I did need to add the time.. it is in the field... just for future reference, if you have a spare moment
Thanks!

 

[ESquared]

I need more information on exactly how you choose the date and time of the 5-year cutoff based on the date and time at each moment, now.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[mrdenny]

When using datediff and between uses the seconds and miliseconds as part of the comparision. It does not round to the day. In order to round to the day, you would need to strip the time from the date to get to midnight. Using the code that Kris11 posted would be the easiest way to do this. Declare a raviable, and set the variable to a date with no time (which defaults to midnight) and do your dateadd and between based on that.

Denny

--Anything is possible. All it takes is a little research. (Me)