NULL problem with date filter - 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

General SQL Server Forums

New to SQL Server Programming

NULL problem with date filter

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-09-15 : 05:30:58

How do people handle filtering a date column if there are null values?

I'm not getting the expected results from my where clause and I think its' the null values which are causing the issue.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 05:36:52

Show us more please.
We cannot see what you're doing.
DateColumns with null values should be ignored in select if no IS NULL in where is written.

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

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-09-15 : 05:41:57

Here is my query:

declare @startDate datetime
declare @endDate datetime

set @startDate = '2004-01-01 00:00:00.000'
set @endDate = '2004-12-20 00:00:00.000'

select
c.irn,
convert (varchar, ce.eventdate, 103) as Abandoned,
convert(varchar, ce1.eventdate, 103) as Abd_Instns_Recd,
n.name as Client,
n1.name as [Owner],
n2.namecode as [Partner],
n3.namecode as Fee_Earner
from cases c
inner join casename cn on cn.caseid = c.caseid and cn.nametype = 'I'
inner join casename cn1 on cn1.caseid = c.caseid and cn1.nametype = 'O'
inner join casename cn2 on cn2.caseid = c.caseid and cn2.nametype = 'SIG'
inner join casename cn3 on cn3.caseid = c.caseid and cn3.nametype = 'EMP'
inner join name n on n.nameno = cn.nameno
inner join name n1 on n1.nameno = cn1.nameno
inner join name n2 on n2.nameno = cn2.nameno
inner join name n3 on n3.nameno = cn3.nameno
left outer join caseevent ce on ce.caseid = c.caseid and ce.eventno = -140
left outer join caseevent ce1 on ce1.caseid = c.caseid and ce1.eventno = 150
where (ce1.eventdate is not null)
or (ce.eventdate is not null)
and cast((str(year(ce.eventdate)) + '/' + str(month(ce.eventdate)) + '/' + str(day(ce.eventdate))) as DATETIME ) >=  cast((str(year(@startDate)) + '/' + str(month(@startDate)) + '/' + str(day(@startDate))) as DATETIME )
and cast((str(year(ce.eventdate)) + '/' + str(month(ce.eventdate)) + '/' + str(day(ce.eventdate))) as DATETIME ) <=  cast((str(year(@endDate)) + '/' + str(month(@endDate)) + '/' + str(day(@endDate))) as DATETIME )
order by 2 asc

I'm using this SQL to create a report in SSRS and the user needs to be able to select a date range for the 'Abandoned' column.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:02:38

I think it is only this:

where (ce1.eventdate is not null)
or and (ce.eventdate is not null)

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

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-09-15 : 06:20:37

Thanks for the reply. Unfortunately I have to use the or because either of the date columns could be NULL for a particular row and I need these rows in the report.

I can't even use isNull because I obviously can't supply an incorrect date. I just can't see a way to handle the NULLs so that I can use the date filter.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:50:19

So you want all rows in the given range AND ignore null values?
Maybe you can give sample data and wanted output in small form?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources