max date

[rds80]

If I have these dates in my startdate column:

2006-08-02 16:51:59.500
2006-08-02 19:58:06.507
2006-08-02 15:26:26.313
2006-08-02 15:31:24.423
2006-08-22 12:35:46.920

and I want to get the maximum date: 2006-08-02 19:58:06.507. How would I get that?

SELECT MAX(StartDate)
FROM tblTest

gets me 2006-08-22 12:35:46.920.

 

[gmmastros]

That is the max date. August 22 is 'larger' than August 2nd.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

If you want to get the max TIME regardless of date, then....

Select Max(StartDate - DateAdd(day, DateDiff(Day, 0, StartDate), 0))
From   tblTest

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

damn it..thanks George

 

[Catadmin]

Ahh. Well, I know what the problem is. Now we just need a solution.

When I ran the following code, I got an interesting result:

Create Table #TempDates (StartDate datetime)

Insert into #TempDates (StartDate)
(Select '2006-08-02 16:51:59.500')
UNION ALL
(Select '2006-08-02 19:58:06.507')
UNION ALL
(Select '2006-08-02 15:26:26.313')
UNION ALL
(Select '2006-08-02 15:31:24.423')
UNION ALL
(Select '2006-08-22 12:35:46.920')

Select StartDate
From #TempDates
Order by StartDate Desc

The Order By statement is not sorting by numerical sorting. And it obviously isn't sorting by military time either... How interesting. It's not even really sorting by AlphaNumeric sorting, because when I change the Desc to Asc, the top value is 2006-08-02 15:26:26.313, which obviously still isn't the 19 you're looking for.

I'm going to have to play with it, but I'm thinking there's going to need to be some sort of looping through the times here to verify which is the greater. Anyone else have any other thoughts?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Yikes. I missed the 02 and 22 thing.

Ignore my post completely. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"