Change your select to do the following. You want to order by the date not
the converted value.

SELECT CONVERT ( char(10), pickedtime , 101 ), 
     COUNT(pickedtime)
FROM OrderHeader
WHERE collection = 1
GROUP BY CONVERT ( char(10), pickedtime , 101 )
order by pickedtime


-----Original Message-----
From: Craig Harrison [mailto:craig.harrison@w...]
Sent: Tuesday, January 15, 2002 7:14 AM
To: sql language
Subject: [sql_language] More dates!!! ENGLISH DATE FORMATTING IN SQL


Dear all,

OK here is yet another SQL Date conversion problem...

My dates, as all of yours are are in the format dd/mm/yyyy hh:mm:ss or 
something like that.

I need to get them into the format DD/MM/YYYY

I have tried this but it does not order them properly because the date 
format is American so the sort is wrong. e.g.:

SELECT CONVERT ( char(10), pickedtime , 101 ), 
     COUNT(pickedtime)
FROM OrderHeader
WHERE collection = 1
GROUP BY CONVERT ( char(10), pickedtime , 101 )
order by CONVERT ( char(10), pickedtime , 101 )

I have also tried building a 'Frankenstein':

CONVERT(varchar, DATEPART(DD, pickedtime)) 
    + '-' + CONVERT(varchar, DATEPART(MM, pickedtime)) 
    + '-' + CONVERT(varchar, DATEPART(YY, pickedtime))

To create a date of 1-11-2001 but it is inelegant and clumsy and doesn't 
order properly anyway because it is only one digit for one digit days.

Does SQL actually handle this at all (how much did they get paid to write 
it?!)?  Please help!

Thanks,

Craig.



$subst('Email.Unsub').