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.
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 datename 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 outputselect 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 descNow 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 givenSuppose '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 tableMadhivananFailing to plan is Planning to fail |
|
|
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 MadhivananFailing to plan is Planning to fail
KH[spoiler]Time is always against us[/spoiler] |
|
|
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 |
|
|
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] |
|
|
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 |
|
|
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 statementhttp://msdn.microsoft.com/en-us/library/aa259213(SQL.80).aspxbut 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 |
|
|
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 queryselect max(case when created_date < @date then created_date end), min(case when created_date > @date then created_date end) from tablewhere created_date is not null KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|