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
 get min date and max date

Author  Topic 

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-08-29 : 06:31:59

hi all,
I want to get the min date and max date from table given a date

name last_date created_date
as 2008-08-21 00:00:00 2008-08-21 00:00:00.000
as 2007-08-21 00:00:00 2007-08-21 00:00:00.000
as 2007-12-12 00:00:00 2007-12-12 00:00:00.000


I tried and got '08/21/2008' as output
select top 1 convert(varchar,last_date,101) from bdetails
where convert(varchar,last_date,101)>convert(varchar,'02/02/2008',101)
and name='as'
order by last_date desc

Now I want to get '12/12/2007' which min date less than '02/02/2008'


Basically I need to get min date and max date from date given
Suppose '02/02/2008' i have to get '08/21/2008' and '12/12/2007'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 06:45:50

select min(case when created_date <@date then created_date end),max(case when created_date >@date then created_date end) from table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-29 : 08:12:56
quote:
Originally posted by madhivanan


select minmax(case when created_date <@date then created_date end),
maxmin(case when created_date >@date then created_date end) from table


Madhivanan

Failing to plan is Planning to fail




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-08-29 : 09:27:54
Thanks for ur reply. But I am getting error as

Warning: Null value is eliminated by an aggregate or other SET operation
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-29 : 09:35:45
it's a warning not error


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

vmurali
Yak Posting Veteran

88 Posts

Posted - 2008-08-30 : 03:04:18
Thanks for ur reply. Can you tell me how to overcome this Warning
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 03:08:29
quote:
Originally posted by vmurali

Thanks for ur reply. Can you tell me how to overcome this Warning


you can turn it off by using SET ANSI WARNINGS OFF statement

http://msdn.microsoft.com/en-us/library/aa259213(SQL.80).aspx

but are you sure you want to do it? it will return un expected values for some operations like divide by zero where it returns NULL value rather than throwing error message.

http://msdn.microsoft.com/en-us/library/aa259213(SQL.80).aspx
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-30 : 03:09:21
That's because your data contain NULL value. It just a warning. You may ignore that or don't include it in your query

select max(case when created_date < @date then created_date end),
min(case when created_date > @date then created_date end)
from table
where created_date is not null



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -