DateTime Convert & OrderBy 1

[jiggyg]

Hello List!

I have a question on how to sort a list of dates after using the convert function on it....

Here's my list of dates:

SELECT DISTINCT runDate
FROM areaEval

returns:
2007-11-01 00:00:00.000
2007-10-01 00:00:00.000
2007-09-05 00:00:00.000
2008-01-01 00:00:00.000
2000-01-01 00:00:00.000
2007-12-01 00:00:00.000

I need to pass this to a report in the format MM/DD/YY and in this order:
01/01/08
12/01/07
11/01/07
10/01/07
09/05/07
01/01/00

If I do this:

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate
FROM areaEval
ORDER BY RunDate DESC

I get:
12/01/07
11/01/07
10/01/07
09/05/07
01/01/08
01/01/00

If I do this, I get the correct order, but the date is NOT in the right FORMAT:

SELECT DISTINCT runDate
FROM areaEval
ORDER BY runDate DESC

Results:
2008-01-01 00:00:00.000
2007-12-01 00:00:00.000
2007-11-01 00:00:00.000
2007-10-01 00:00:00.000
2007-09-05 00:00:00.000
2000-01-01 00:00:00.000

How can I get this in the right ORDER and FORMAT???

Any ideas/help would be greatly appreciated!!!

Thanks much!
-jiggyg

 

[kaht]

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate
FROM areaEval
ORDER BY [s]RunDate[/s] [!]r[/!]unDate DESC

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B>[/small]

 

[PWise]

try

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY RunDate DESC

 

[gmmastros]

It appears as though pwise has come up with a viable solution. However, you do need to be careful with this because multiple records with the same data but a different time will cause you to get extra rows returned that you were not expecting.

Ex:

Set DATEFORMAT MDY
Declare @Temp Table(runDate DateTime)

Insert Into @Temp Values('2007-11-01 00:00:00.000')
Insert Into @Temp Values('2007-10-01 00:00:00.000')
Insert Into @Temp Values('2007-09-05 00:00:00.000')
[!]Insert Into @Temp Values('2008-01-01 00:00:00.000')
Insert Into @Temp Values('2008-01-01 10:00:00.000')[/!]
Insert Into @Temp Values('2000-01-01 00:00:00.000')
Insert Into @Temp Values('2007-12-01 00:00:00.000')

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM   @Temp
ORDER BY RunDate DESC

There is a way to make this work, and I suspect that either kaht or pwise will be able to come up with an answer.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

You could try (untested):

SELECT DISTINCT CONVERT(varchar,runDate,1) AS Run_Date
FROM areaEval
ORDER BY year(runDate) desc, mon(runDate) desc, day(runDate) DESC

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Sis....

That doesn't work because of the distinct.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

try

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate ,convert(datetime(CONVERT(varchar,runDate,1)) as Dateorder
FROM areaEval
ORDER BY convert(datetime(CONVERT(varchar,runDate,1)) DESC

not tested

 

[gmmastros]

pwise....

That seems to work properly (with the obvious syntax errors corrected).

I was thinking along these lines.

Select Convert(varchar(8), RunDate, 1)
From   (
       Select Distinct DateAdd(Day, DateDiff(Day, 0, RunDate), 0) As RunDate
       From   areaEval
       ) As A
Order By RunDate DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jiggyg]

Thanks for all the replies and help!!! You guys are awesome!

SELECT DISTINCT CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC

Works like a charm!

Thanks again!
-jiggyg

 

[jiggyg]

If I want to get the top 1 from the list, how can I do that?

SELECT distinct CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC

Returns:
01/01/08 2008-01-01 00:00:00.000
12/01/07 2007-12-01 00:00:00.000
11/01/07 2007-11-01 00:00:00.000
10/01/07 2007-10-01 00:00:00.000
09/05/07 2007-09-05 00:00:00.000
01/01/00 2000-01-01 00:00:00.000

Tried:

SELECT TOP 1 RunDate
FROM
(
SELECT distinct CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC
)

and get error msg:

Msg 1033, Level 15, State 1, Line 7
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.



Thanks in advance for your time and expertise!
-jiggyg

 

[jiggyg]

Got it....

SELECT top 1 RunDate
FROM
(
SELECT distinct top 2147483647 CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC
)sub

Apparently in MS SQL Server 2005, since you can't use 'TOP 100%', you need to use that crazy number (max number of rows returned by a query) in the inner select.

Thanks!

 

[gmmastros]

>> Apparently in MS SQL Server 2005, since you can't use 'TOP 100%', you need to use that crazy number (max number of rows returned by a query) in the inner select.

No. That's not exactly correct. You just need to spell it out.

SELECT top 1 RunDate
FROM
(
SELECT distinct top [!]100 Percent[/!] CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC
)sub

Of course, you don't need to use a derived table for this. You could simply do this....

SELECT top 1 CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM @Temp
ORDER BY Dateorder DESC

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jiggyg]

Hmmm...When I spell it out, I get 10/01/07 returned as my TOP 1.

SELECT top 1 RunDate
FROM
(
SELECT distinct top 100 percent CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC
)sub

Returns:
10/01/07

But, the Inner select is working correctly!

SELECT distinct top 100 percent CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM areaEval
ORDER BY Dateorder DESC

Returns:
01/01/08 2008-01-01 00:00:00.000
12/01/07 2007-12-01 00:00:00.000
11/01/07 2007-11-01 00:00:00.000
10/01/07 2007-10-01 00:00:00.000
09/05/07 2007-09-05 00:00:00.000
01/01/00 2000-01-01 00:00:00.000

 

[gmmastros]

What do you get when you run this...

SELECT RunDate
FROM
(
SELECT top 1 CONVERT(varchar,runDate,1) AS RunDate ,RunDate as Dateorder
FROM @Temp
ORDER BY Dateorder DESC
)sub

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This is even better. Since you only want the top 1, then you don't need to worry about distinct. Also, for ordering purposes, it's probably better to convert you mm/dd/yy format in the outer select.

Like this..

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]varchar[/color](8), RunDate, 1) [COLOR=blue]As[/color] RunDate
[COLOR=blue]FROM[/color]  (
      [COLOR=blue]SELECT[/color] top 1 RunDate
      [COLOR=blue]FROM[/color] areaEval
      [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] RunDate [COLOR=#FF00FF]DESC[/color]
      )sub

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom