CODEWITHSUNDEEP
sqlsql-server
Taufiq Abdur Rahman
Taufiq Abdur Rahman

Reputation: 1388

SQL Server Optional Parameter Date from to

I have a stored procedure and I want to search between the from and to date, but:

if @FromDate is not null and @ToDate is not null then 
vSaleDetail.date  between @FromDate and @ToDate 

if @FromDate is not null and @ToDate is null then
vSaleDetail.date = @FromDate

if @FromDate is null and @ToDate is then
Return All

This is what I have but it is not working

where
(((@FromDate is null and @ToDate is null) OR  (vSaleDetail.date  between @FromDate and @ToDate ))
AND ((@FromDate is null) OR  (vSaleDetail.date = @FromDate)))

Please help — what do I need to do to fix it?

Upvotes: 1

Views: 1194

Answers (5)

Global Developer's Academy
Global Developer's Academy

Reputation: 1

AND (@DateFrom IS NULL OR CONVERT(DATETIME, [CreationDate], 103) BETWEEN CONVERT(DATETIME, @DateFrom, 103) AND DATEADD(DAY, 1, CONVERT(DATETIME, ISNULL(@DateTo, @DateFrom), 103)))

Upvotes: 0

HLGEM
HLGEM

Reputation: 96610

I would poulate the null parameters with dates way outside the range you need. So If form date is null, I would populate with the earlies t accepatable date in your database's datetime data type. The To date would be poulated with the latest possible date. Then you don't need to use all that complex logic which slows things down.

IF @FromDate is null
Begin 
Set @Fromdate = '17530101'
END

IF @ToDATE is null
BEGIN 
SET @Todate = '99991231'
END

select ...
WHERE  vSaleDetail.date  >=@FromDate and <= @ToDate

Upvotes: 3

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

declare @default_min_date datetime;
declare @default_max_date datetime;

SET @default_min_date = 0
SET @default_max_date = 2958463;

SELECT  *
FROM    MyTable
WHERE
        myDateColumn >= Isnull(@DateFrom, @default_min_date) AND myDateColumn <= Isnull(@DateTo, @default_max_date)

Upvotes: 0

coge.soft
coge.soft

Reputation: 1674

You can try this:

WHERE (vSaleDetail.date BETWEEN @FromDate AND ISNULL(@ToDate,@FromDate)
    OR COALESCE(@FromDate, @ToDate) IS NULL)

ISNULL(p1, p2):

if p1 IS NULL then p2 is returned, otherwise p1 is returned

COALESCE(p1, p2, ...):

Like ISNULL(). returns p1 unless it is NULL, in which case, p2 is returned unless it is NULL, in which case p3.... if all parameters in COALESCE() are null, NULL is returned.

Upvotes: 1

Jasmina Shevchenko
Jasmina Shevchenko

Reputation: 797

Try this:

    WHERE
    ( 
        ( @FromDate IS NULL AND @ToDate IS NULL)
        OR 
        ( vSaleDetail.date BETWEEN @FromDate AND @ToDate )
    )
    OR <-- @FromDate IS NULL AND @FromDate IS NULL
( @FromDate IS NULL OR vSaleDetail.date = @FromDate )

Upvotes: 0

Related Questions