Date Time Range Question 1

[AGus01]

Hi i am using SQL Server 2000.

Hi i have what must be an easy date time question however i cant work out why this will not function. I Wish to check if a current date is valid. ie. greater than OfferStartDate and less than OfferFinishDate. However the query bellow returns no records. Any ideas why?

The db data

OfferID  OfferCode    OfferStartDate              OfferFinishDate    
------- ------------ -------------------------   -------------------------- 
10      tester        2005-09-13 10:30:19.873     2005-10-04 00:00:00.000

DECLARE @CurrentDate datetime
SELECT @CurrentDate = GetDate()

PRINT @CurrentDate

SELECT 
	[O].OfferID,
	[O].OfferCode,
	[O].OfferStartDate,
	[O].OfferFinishDate
 FROM 
	Offer AS [O]
JOIN
	OfferSubCategory AS [OS]
ON 
	[O].[OfferID] = [OS].[OfferID]
WHERE 
	[O].OfferCode = 'tester'
AND
	[O].OfferStartDate > @Currentdate
AND
	[O].OfferFinishDate < @CurrentDate

thanks

--gus

 

[gmmastros]

I think you have your less than and greater thans backwards.

    [O].OfferStartDate [red]<[/red] @Currentdate
AND
    [O].OfferFinishDate [red]>[/red] @CurrentDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AGus01]

Doe.

Thanks.

 

[gmmastros]

A word of caution here. When doing date range searches on fields that have a time component, you need to be very careful.

For example, if your OfferFinishDate is today, and the time component is '00:00:00.000', then this record will not appear because GetDate() also returns the time and Today @ 00:00:00.000 is less than GetDate().

If this is what you want, then fine. This is just of word of caution intended to get you to think about your query a little more. Feel free to ignore this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom