CODEWITHSUNDEEP
sqlt-sqlreporting-services
Sewder
Sewder

Reputation: 754

Date Paramter won't work in SQL for ISNULL column

I have a table with a date column in it. I am currently using ISNULL to set all NULL date values to a 1/1/2001 so it looks like so.

Select ISNULL(DateColumn,'1/1/2001') AS DateColumn, PartNumber
From Table
Where DateColumn < '1/1/1990'

It seems the created date '1/1/2001' appears on any generated list , regardless of the filter.

Any suggestions?

Upvotes: 1

Views: 69

Answers (1)

gotqn
gotqn

Reputation: 43646

That's because the WHERE clause is working with the initial DateColumn value, befote the 

ISNULL(DateColumn,'1/1/2001')

is executed.

There are several ways to fix this, one of them is to change the where clause like this:

Where ISNULL(DateColumn,'1/1/2001') < '1/1/1990'

I am not sure how your real situation looks like, you can firstly check for NULL values and then filter the result using sub-query.

Other solution will be to transform the where clause like this:

Where DateColumn < '1/1/1990' AND DateColumn IS NOT NULL

Upvotes: 1

Related Questions