Null Date Records

[codrutza]

I have in a select

and
(
t.aDate<=getdate() or t.aDate is null or…year????
)


Can/how to write in the same select year(t.adate) >= year(getdate()) or year(t.adate) is null?
Because when I refer the year(t.adate) doesn’t take the null records.

Thank you

 

[Olaf Doschke]

Well, put the check of t.adate IS NULL first, then further clauses are not computed , if the column is null and you get no error from any expressions not able to deal with NULL.
On the other hand many expressions about NULL are simply returning NULL and so is [tt]SELECT YEAR(NULL)[/tt]. That makes me wonder, what problems you really have. SQL Server is even clever enough to not check column>value for NULL colums, eg see following test:

declare @test as table (nulcol datetime null);
insert into @test (nulcol) values ('20150101'),('20160101'),(null)

Select * from @test where Year(nulcol)<year(getdate())
Select * from @test where Year(nulcol)>2015

This does neither error, nor return the null row. So you actually don't have to worry too much. A NULL result from a boolean expression is neither true nor false. Also it's negation.

Bye, Olaf.

 

[codrutza]

Thank you, Olaf. I had a logic problem, I think.
I put now:
and
(
(t.aDate <=getdate() and year(t.aDate)>=year(getdate()))
or (t.aDate) is null
)
and retrieving the records t.aDate null, I retrieve the records with the year(t.aDate) null.

I'm not totally sure this is correct. What do you think?

 

[Olaf Doschke]

Well,

you want the year of a stored date (t.aDate) to be at least the current year with the >= comparison and at the same time it should be lower than current datetime, that only matches to current year past dates, dates from January to March and half April.

Besides that range you allow NULLs.

If you only get NULLS, then you have no dates in the current year up to current day, only undated records.

What range do you want to filter?

Bye, Olaf.

 

[Olaf Doschke]

To get an overview of dates you have, how about counting in a quarterly overview:

select year(adate) as aYear, datepart(quarter,adate) as aQuarter, count(*) as [Rows in that Quarter]
From yourtable
group by year(adate), datepart(quarter,adate)

If you get data for the time span you're interested, then maybe some other condition of the overall query has to be addressd.

Bye, Olaf.