searching for the 2 most near days the a gicen dae

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

SQL Server Development (2000)

searching for the 2 most near days the a gicen dae

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-11 : 08:48:45

i have a table which 1 of its columns is a DATETIME column

i want to find the most near date to a given date

where i want :1 date that is before the given date and 1 dat is after the given date and each 1 of them are the most closer to the giiven date)

thnaks in advance

peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 08:52:23

select top 1 yourdatetimecolumn
from yourtable
order by abs(datediff(day, yourdatetimecolumn, @yourdatetimevariable))

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:22:13

or maybe this

SELECT	d.ma NearestLowerDate,
	d.mi NearestHigherDate,
	CASE WHEN ABS(DATEDIFF(second, d.ma, @YourDatetimeVariable)) < ABS(DATEDIFF(second, d.mi, @YourDatetimeVariable)) THEN d.ma ELSE d.mi END NearestOverallDate
FROM	(
		SELECT	MAX(YourDatetimeColumn) ma
		FROM	YourTable
		WHERE	YourDatetimeColumn <= @YourDatetimeVariable
		UNION ALL
		SELECT	MIN(YourDatetimeColumn) mi
		FROM	YourTable
		WHERE	YourDatetimeColumn >= @YourDatetimeVariable
	) d

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 15:05:47

How did it went with this suggestion?
Any progress yet?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources