| Author |
 Topic  |
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
 Posted - 05/28/2012 : 05:13:52
|
Please Help!!
I have a list of dates and I have a parameter date to compare with these dates and I want to get the future closest one if
Parameter date: 2011-02-15 00:00:00
List:
2010-11-03 00:00:00
2010-12-04 00:00:00
2011-01-04 00:00:00
2011-02-04 00:00:00
2011-03-07 00:00:00
2011-04-07 00:00:00
2011-05-08 00:00:00
2011-06-08 00:00:00
2011-07-09 00:00:00
2011-08-09 00:00:00
Result: 2011-03-07 00:00:00 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/28/2012 : 05:19:17
|
select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))
KH
Time is always against us
|
 |
|
|
Villanuev
Constraint Violating Yak Guru
309 Posts |
Posted - 05/28/2012 : 05:23:25
|
Please try.
Declare @tran datetime
Set @tran='2011-02-15 00:00:00'
DECLARE @T TABLE (trandate datetime)
INSERT @T (trandate)
Values
('2010-11-03 00:00:00'),
('2010-12-04 00:00:00'),
('2011-01-04 00:00:00'),
('2011-02-04 00:00:00'),
('2011-03-07 00:00:00'),
('2011-04-07 00:00:00'),
('2011-05-08 00:00:00'),
('2011-06-08 00:00:00'),
('2011-07-09 00:00:00'),
('2011-08-09 00:00:00')
Select min(trandate) from @T
where trandate > @tran |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/28/2012 : 13:49:58
|
quote:
Originally posted by khtan
select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))
KH
Time is always against us
This will give even dates less than passed date if difference is close enough than future date which i dont think is what OP's looking at
I have a parameter date to compare with these dates and I want to get the future closest one
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
chriztoph
Posting Yak Master
Philippines
155 Posts |
Posted - 05/28/2012 : 20:17:31
|
Thanks guys for the help..
maybe I'll use Villanuev's..
To khtan and visakh16, thanks for giving other option..I can use this in the other projects.. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/28/2012 : 21:26:45
|
quote:
Originally posted by visakh16
quote:
Originally posted by khtan
select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))
KH
Time is always against us
This will give even dates less than passed date if difference is close enough than future date which i dont think is what OP's looking at
I have a parameter date to compare with these dates and I want to get the future closest one
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
thanks for pointing out. I missed that
KH
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/29/2012 : 01:27:53
|
quote:
Originally posted by khtan
quote:
Originally posted by visakh16
quote:
Originally posted by khtan
select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))
KH
Time is always against us
This will give even dates less than passed date if difference is close enough than future date which i dont think is what OP's looking at
I have a parameter date to compare with these dates and I want to get the future closest one
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
thanks for pointing out. I missed that
KH
Time is always against us
No problem
you're wc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Closest date
