CODEWITHSUNDEEP
sqlsql-serverdatetime
Web Develop Wolf
Web Develop Wolf

Reputation: 6326

Optional DateTime Parameter in SQL

I have set up an SQL Stored Procedure where the parameters are optional as in the front end any combination of these filters can have values or not have values. The only problem is when I come pass a null date in. SQL produces an error if I leave this null, however I have managed to do some reading around the problem and found that using the following should resolve the error:

SET @SessionStarted = ISNULL(@SessionStarted, GETDATE())

Which it does however, now it's not returning records it should be returning because it's only looking for records with the current date and time.

The parameter is defined as such:

@SessionStarted datetime = NULL,

And the where clause for this particular parameter is:

(@SessionStarted IS NULL OR VisitDate = @SessionStarted)

Is there anyway round this issue when a value for the can't be specified?

Upvotes: 0

Views: 1772

Answers (2)

Ananda Kumar Jayaraman
Ananda Kumar Jayaraman

Reputation: 399

When you assign current date time to a variable (like below) SET @SessionStarted = ISNULL(@SessionStarted, GETDATE())

and compare it (like below) against a datetime dattyped column, which will never equal and will not return records.

(@SessionStarted IS NULL OR VisitDate = @SessionStarted)

You should make sure that what value your datetime column holds and what is your expected default value to be assigned to the variable in case NULL value is passed in.

Upvotes: 0

Stephen S.
Stephen S.

Reputation: 1656

You could just use a different date in your isnull and detect it. Something like '1900-01-01' feels like a good "known invalid" day to use in place of your NULL.

Upvotes: 1

Related Questions