Datetime Parameter

Question

Datetime Parameter

pbyrum

SSCrazy

Points: 2785
More actions
May 21, 2008 at 5:56 pm

#191358

I have a Stored Procedure and a table with a date column in it. I am filtering on the date column with a WHERE clause in a SELECT statement. I also have 1 parameter that is a datetime type. The value in the parameter may have a valid date in it, but sometimes the value maybe empty.
When the parameter is empty, how do I write the T-SQL to tell the WHERE clause to get all dates? For example:
SELECT OrigBondDate
FROM BondTable
WHERE
convert(smalldatetime,OrigBondDate) = @BondDate
This works great if there is a date in the parameter, but what if there is no date? If there is no date in the parameter, can T-SQL tell the server to get all dates in the column?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

First, the CONVERT on the datetime column will kill any chance of using an Index SEEK if the column is indexed. Second, you just need to add a simple condition to achieve what you want... both are combined in the following code...

SELECT OrigBondDate

FROM BondTable

WHERE ( OrigBondDate >= @BondDate

AND OrigBondDate < @BondDate +1)

OR @BondDate IS NULL

Of course, @BondDate should be a DateTime variable...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pbyrum SSCrazy Points: 2785 More actions · Answer 2

pbyrum

SSCrazy

Points: 2785

May 21, 2008 at 10:37 pm

#818982

Thanks, that did it.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

My pleasure. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)