Get today's date in qry.....(and or is null)

[robmason10]

Help getting correct statement for following:

SELECT * FROM tblCapacity WHERE startDate < {todays date} AND endDate > ({todays date} or IS NULL)

tx in advance

 

[marcarls]

Perhaps something like this?

SELECT *
FROM tblCapacity
WHERE startDate < getdate()
AND (endDate > getdate()
or endDate IS NULL)

/Maria

 

[ecobb]

Actually, getDate() will return the current Date AND Time.
If I'm not mistaken, SQL Server will read a time of 00:00:00 if you don't have one specified. So, if your StartDate is a date only (no time), and getDate() returns the current Date and Time, your StartDate would be 11/14/2003 00:00:00 and your getDate() would be 11/14/2003 08:33:00, which makes your StartDate < getDate()

Try this:

Declare @CurrentDate smalldatetime
Set @CurrentDate = getDate()

SELECT *
FROM tblCapacity
WHERE startDate < (CONVERT(VARCHAR(10),@CurrentDate,101))
AND (endDate > (CONVERT(VARCHAR(10),@CurrentDate,101))
or endDate IS NULL)

This will convert the getDate value into the Date only, no time.

Hope This Helps!

Ecobb
- I hate computers!