Search and Date is NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Search and Date is NULL

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-05 : 22:06:29

In the WHERE clause it is possible to ignore a param if it is null or blank or whatever...

I am still having trouble with a DATE.

WHERE (@dt IS NULL OR (pk.CreateDate BETWEEN CAST(@dt AS DATETIME)
AND CAST(@dt + ' 23:59:59' AS DATETIME)))

If @dt is NULL or it will be an empty string, it breaks.....
Empty STRING!!!

Suggestions?

Zath

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-11-05 : 22:39:03

would ISNULL function work?

WHERE ISNULL(@dt,'1753-01-01 00:00:00.000')...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-06 : 09:38:15

replace this
@dt IS NULL
with
nullif(@dt,'') IS NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-06 : 09:39:59

run this

declare @dt datetime
set @dt = NULL
select @dt
set @dt=''
select @dt
select nullif(@dt,'')

to see the behaviour if the datetime is NULL or empty string

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 05:28:39

There can't be empty value for a DATETIME,INT,etc column. Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/02/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 06:01:33

But if a datetime parameter is passed as empty string then
NULLIF(@parm,'')
will work

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-11-08 : 06:48:19

To avoid parameter sniffing, do this...


create procedure ....
(
   @Date VARCHAR(300)
)
AS

SET NOCOUNT ON

DECLARE @FromDate DATETIME, @ToDate DATETIME

IF ISDATE(@Date) = 1 and @Date > ''
   SET @FromDate = CAST(@Date AS DATETIME)
else
   SET @FromDate = '17530101'

IF @FromDate = '17530101'
   SET @ToDate = '99991231'
else
   SET @ToDate = DATEADD(DAY, 1, @FromDate)

SELECT
.....
WHERE pk.CreateDate >= @FromDate AND pk.CreateDate < @ToDate

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources