Ordering a converted date_stamp

[seekwall]

I am trying to run a simple query over a datestamp field defaulted with getdate(), using the convert function:

select convert(varchar(10),datestamp,103), count(*)
from table
group by convert(varchar(10),datestamp,103)
order by convert(varchar(10),datestamp,103)

The problem is that the results are only ordered by the DD part of the date.

Is there any simple solution to this problem ?

 

[RickCole]

Try this:

select convert(varchar(10),datestamp,103), count(*)
from table
group by datestamp
order by datestamp


Rick.

 

[tlbroadbent]

Or try this. It does require SQL 7 or higher. The database must also be in SQL 7 or higher compatibility mode.

Select
datestamp=Convert(char(10),
convert(datetime,datestamp, 120), 103),
reccnt
From
(Select TOP 100 Percent
datestamp=convert(char(10), datestamp, 120),
reccnt=count(*)
From tblname
Group By convert(char(10), datestamp, 120)
Order By convert(char(10), datestamp, 120)) As qry Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm