MAX and MIN values of datetime field

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)

MAX and MIN values of datetime field

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-07-26 : 12:24:11

I have the following information in a table:



id           ddate
1      08/01/2002 12:21:27
2      08/01/2002 16:57:16
3      08/01/2002 16:59:25
4      09/01/2002 09:21:27
5      09/01/2002 12:59:17

I am trying to get the max/ min values for each day so the result set would be something like this:

date           maxdate                   mindate
08/01/2002     08/01/2002 16:59:25       08/01/2002 12:21:27
09/01/2002     09/01/2002 09:21:27       09/01/2002 12:59:17

There are around 50,000 rows in this table which comprises of daily
data for at least two year, therefore having in excess of 700 days.

Any help or pointers would be appreciated

Thanxs

OMB

Edited by - omb on 07/26/2002 12:24:48

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-07-26 : 12:47:32

SELECT a.ddates, Max(b.ddate) maxdate, Min(b.ddate) mindate
FROM (Select Distinct cast(Month(ddate) as varchar(2))+'/'+ cast(Day(ddate) as varchar(2))+'/'+cast(Year(ddate) as varchar(4)) ddates
from ddates) a INNER JOIN
ddates b
ON a.ddates = cast(Month(b.ddate) as varchar(2))+'/'+ cast(Day(b.ddate) as varchar(2))+'/'+cast(Year(b.ddate) as varchar(4))
Group by a.ddates

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources