Closest date - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Closest date

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2012-05-28 : 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)

17689 Posts

Posted - 2012-05-28 : 05:19:17

[code]
select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))
[/code]

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 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

52326 Posts

Posted - 2012-05-28 : 13:49:58

quote:
Originally posted by khtan

select top 1 *
from yourtable
order by abs(datediff(day, datecol, parameter_date))

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

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

184 Posts

Posted - 2012-05-28 : 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)

17689 Posts

Posted - 2012-05-28 : 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
[spoiler]Time is always against us[/spoiler]

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
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 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
[spoiler]Time is always against us[/spoiler]

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
[spoiler]Time is always against us[/spoiler]

No problem
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources