Combining 2 fields for a date range

[netcert]

I have 2 fields: Begin_Date and Thru_Date

I need to pull records only where the current date (getdate?) is within the range of the Begin_Date and Thru_Date. (01/01/xx thru 12/31/xx)

The Thru_Date is never prior to the Begin_Date. What is the best way to structure the SQL stmt to do this?

WHERE.....activity.effective_date <= getdate() and activity.thru_date >=getdate()

Thanks!

 

[deepsheep]

Try this:

where getdate() between effective_date and thru_date

I've found 'between' to be much more efficient (especially with datetimes) than < and > even though the outcome is the same.
Just ensure your datetime fields really are datetime fields and not char fields and it should work fine. (Don't laugh, I've seen it!)

Hope it helps!

 

[SQLBill]

The problem with your 'solution' is that GETDATE() is selected twice and each time it will have a different value.

Create a variable (DECLARE @mydate DATETIME) and SET it to the GETDATE() value (SET @mydate = GETDATE()). Then when you use the value in the WHERE, it will be the same in both criteria.

WHERE activity.thru_date >= @mydate
AND activity.effective_date <= @mydate

One issue that might come up....GETDATE() returns the date AND time. So, you might not get all the values you want....it depends on what you are really trying to do.

-SQLBill



Posting advice: FAQ481-4875