CODEWITHSUNDEEP
sqlsql-servert-sql
neeko
neeko

Reputation: 2000

Show all records if dates are null in SQL query

SET @dateFrom = ''
SET @dateTo = ''

SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
WHERE [tNumber] LIKE '%' 
AND [createdDate] > @dateFrom+'%' 
AND [createdDate] < @dateTo+'%'

I am trying to get the query to show all values from the database if the dates are blank or if @dateFrom and @dateTo has a value then it should filter those specific results

Upvotes: 2

Views: 4870

Answers (6)

Aravind Goud
Aravind Goud

Reputation: 120

To show the Records with dates are null you can use the below query like this 

select * from [webtable] where ID not in (Select ID from [webtable] where  [createdDate] > COnvert(Date, ''))

Upvotes: 0

coge.soft
coge.soft

Reputation: 1674

SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
WHERE [tNumber] LIKE '%' 
AND (
        (       [createdDate] > @dateFrom --removed +'%'
            AND [createdDate] < @dateTo ) --close 2nd parentheses and removed +'%'
        OR      COALESCE(@dateFrom,@dateTo) IS NULL
    ) --close 1st parantheses

COALESCE() evaluates one thing after another within its parentheses until it doesn't find a NULL value. If all values are NULL, it returns NULL. With the way I wrote this statement, if both parameters are NULL, nothing is filtered by [createdDate]

UPDATE

removed +'%'

Upvotes: 2

user2919277
user2919277

Reputation: 246

TRY this 

    SET @dateFrom = ''
    SET @dateTo = ''

    SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
    WHERE [tNumber] LIKE '%' 
    AND CASE 
            WHEN (@dateFrom = '' OR @dateTo = '') THEN ([createdDate] LIKE '%')
        ELSE  ([createdDate] BETWEEN @dateFrom and @dateTo)
        END

Upvotes: 1

db9dreamer
db9dreamer

Reputation: 1715

SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
WHERE [tNumber] LIKE '%' 
AND (@dateFrom = '' OR [createdDate] > @dateFrom+'%')
AND (@dateTo = '' OR [createdDate] < @dateTo+'%')

Upvotes: 0

R S P
R S P

Reputation: 1077

Try this :

SET @dateFrom = ''
SET @dateTo = ''
IF(@dateFrom = '' AND @dateTo='')
BEGIN
SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
END
ELSE
BEGIN
SELECT [tNumber], [createdDate], [Total], [pfAmount] FROM [webtable]
WHERE [tNumber] LIKE '%' 
AND [createdDate] > convert(DATE,@dateFrom) 
AND [createdDate] < COnvert(Date,@dateTo)
END

Upvotes: 1

Raghuveera
Raghuveera

Reputation: 320

 declare @fdate datetime
 declare @tdate datetime
 set @fdate=''--'2013-10-10'
 set @tdate=''--'2013-10-10'
 select * from yourTable where (datefield>@fdate or @fdate='') and (datefield<@tdate or @tdate='')

try this..

Upvotes: 0

Related Questions