select the middle 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 2005 Forums

Transact-SQL (2005)

select the middle date?

Author

Topic

markj11
Starting Member

17 Posts

Posted - 2008-10-16 : 13:25:06

I have 3 dates in the table:
08/01/2008
09/01/2008
10/01/2008

I pass in the dates 08/01/2008 and 11/01/2008.
How do I return 09/01/2008?

This is the current code which is incorrect:


select top 1 date 
from table 
where date >= '08/01/2008'
   and date < '11/01/2008' 
order by date desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:32:46

[code]select date
from
(
select date,row_number() over (order by date) as seq,count(date) over() as total
from table
where date >= '08/01/2008'
and date < '11/01/2008'
)t
where seq=cieling(total/2.0)[/code]

markj11
Starting Member

17 Posts

Posted - 2008-10-16 : 13:44:48

Thanks, I should have posted in 2000 since we have still have to support it.
CEILING

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:52:15

try this if using sql 2000

CREATE TABLE #Temp
(ID int identity(1,1),
date datetime
)

INSERT INTO #Temp (date)
select date
from table 
where date >= '08/01/2008'
and date < '11/01/2008'
order by date

SELECT t.*
FROM #Temp t
CROSS JOIN (SELECT COUNT(*) AS reccnt FROM #Temp)t1
WHERE t.ID=CIELING(t1.recnt/2.0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources