NULL Date fields - terryshami

Hi

How can I get around doing MAX(DateField) where datefield is a
datetime NULLABLE column.

This raises the warning  causing warning
Warning: Null value is eliminated by an aggregate or other SET
operation.

I don't like setting ansi-warnings off, because it will hide other
warnings.

Does ansi-warnings only effect scope of SP its execute in?

I've got around it by using a temp table and updates but its 3
separate steps.

E.G.
create  table #t1
(id int not null,  datea datetime null,  val int null)
go
insert into #t1 values(1, getdate(),2)
insert into #t1 values(1, null,3)
insert into #t1 values(2, null,6)
insert into #t1 values(2, null,7)

select id, max(datea) as df, sum(ISNULL(val,0)) as v
from #t1
group by id

id          df                      v
----------- ----------------------- -----------
1           2007-11-05 ###       5
2           NULL                    13

NULL Date fields - Andrei

Hi Terry,

You could try this :
select max(isnull(datea, '1900/01/01')) from #t1

You can replace '1900/01/01' with any small date, according to the dates you
have in your tables.

Andrei.

NULL Date fields - Madhivana

Why are worrying about warnings?
You can also use
select max(isnull(datea, '')) from #t1

NULL Date fields - Madhivanan

On Nov 6, 2:37 pm, Madhivanan <Madhiva...@>

or

select id,max(isnull(datea, '')), sum(ISNULL(val,0)) as v  from #t1
group by id

NULL Date fields - terryshami

Didn't think that would work - but I get

id          df                                         v
----------- -----------------------                    -----------
1           2007-11-06 10:50:58.640      5
2           1900-01-01 00:00:00.000      13

its replaced empty string with first date of smalldatetime which I was
not expecting.

As previous poster said I could use  max(ISNULL(datea, '18000101')) -
then update to NULL. This may

I'm worried about the warning because the App that uses this proposed
SP is a black box and will raise an error on warnings.

NULL Date fields - Madhivanan

Yes you can then replace 1900-01-01 00:00:00:000 with NULL when you
display at front end

NULL Date fields - Hugo Kornelis

Or do it in the back end:

SELECT   id,
NULLIF(MAX(COALESCE(datea, '19000101')),'19000101'),
NULLIF(SUM(COALESCE(val,0)),0) AS v
FROM     #t1
GROUP BY id;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis