TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft SQL Server

NULL Date fields

Microsoft SQL Server

 
 
 
Thread Tools Display Modes
Old 05-11-2007, 06:09 PM   #1
terryshamir@gmail.com
Guest
 
Posts: n/a
NULL Date fields

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

 
Old 05-11-2007, 07:07 PM   #2
Andrei
Guest
 
Posts: n/a
Re: NULL Date fields

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.


<terryshamir***********> wrote in message
news:1194286146.880067.301130@o38g2000hse.googlegr oups.com...
> 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
>



 
Old 06-11-2007, 09:04 AM   #3
Madhivanan
Guest
 
Posts: n/a
RE: NULL Date fields


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


"terryshamir***********" wrote:

> 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
>
>

 
Old 06-11-2007, 09:04 AM   #4
Madhivanan
Guest
 
Posts: n/a
Re: NULL Date fields

On Nov 6, 2:37 pm, Madhivanan <Madhiva...@discussions.microsoft.com>
wrote:
> Why are worrying about warnings?
> You can also use
> select max(isnull(datea, '')) from #t1
>
>
>
> "terrysha...***********" wrote:
> > 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- Hide quoted text -

>
> - Show quoted text -


or

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

 
Old 06-11-2007, 10:02 AM   #5
terryshamir@gmail.com
Guest
 
Posts: n/a
Re: NULL Date fields


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


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.

 
Old 07-11-2007, 01:06 PM   #6
Madhivanan
Guest
 
Posts: n/a
Re: NULL Date fields

On Nov 6, 3:55 pm, terrysha...*********** wrote:
> > select id,max(isnull(datea, '')), sum(ISNULL(val,0)) as v from #t1
> > group by id

>
> 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.



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

 
Old 07-11-2007, 10:09 PM   #7
Hugo Kornelis
Guest
 
Posts: n/a
Re: NULL Date fields

On Wed, 07 Nov 2007 05:02:14 -0800, Madhivanan wrote:

>On Nov 6, 3:55 pm, terrysha...*********** wrote:
>> > select id,max(isnull(datea, '')), sum(ISNULL(val,0)) as v from #t1
>> > group by id

>>
>> 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.

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


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
 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 05:35 AM.


vBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO
Copyright © 2005-2013, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional