Sql Server SELECT statement with a "bad" date condition

Say, if I nee开发者_JS百科d to select records from the database where the date field is in a particular time frame. The select query would be this:

SELECT * FROM [MyTable] WHERE [Date] >= @RangeBegin AND [Date] <= @RangeEnd;

But I have a little condition when the Date field might be empty or invalid. In that case the date value should be taken from @DefaultDate.

How do you put all this into SQL?

Since you say that the date can be invalid I assume you have stored dates as varchar. You can use isdate to check if the date is valid.

select case isdate([Date]) 
         when 1 then cast([Date] as datetime)
         else @DefaultDate 
       end as [Date]
from MyTable
where case isdate([Date]) 
        when 1 then cast([Date] as datetime)
        else @DefaultDate 
      end between @RangeBegin and @RangeEnd

I don't see how a Date field might be invalid. But, for null value, you can do the following :

SELECT * 
FROM [MyTable] 
WHERE (     [Date] IS NOT NULL 
        AND [Date] >= @RangeBegin AND [Date] <= @RangeEnd
      )
OR    (     [Date] IS NULL 
        AND @DefaultDate >= @RangeBegin AND @DefaultDate <= @RangeEnd
      )

SELECT CASE WHEN ([Date] IS NULL) THEN @DefaultDate ELSE [Date] END AS [Date]
FROM [MyTable]
WHERE ([Date] >= @RangeBegin AND [Date] <= @RangeEnd) OR ([Date] IS NULL)

select case when isdate([Date]) = 0 then @DefaultDate else [Date] end from ... where Date between @RangeBegin and @RangeEnd or isdate([Date]) = 0

Case statement in the where clause should be avoid whenever possible.