Mik Muller wrote: > > select left(datetime,11), count(*) as dt > from sitelog > where datetime >= '2007-02-03 00:00' > group by left(datetime,11) > order by left(datetime,11) desc > > > Well, now that I look at it, the order by is ordering by "jan 1 2006" etc in > desc alphabetical order. How do I get this field to be sorted by yyyy-mm-dd? > Do I have to go through all this... > > http://www.databasejournal.com/features/mssql/article.php/10894_2197931_2 >
Read up on the CONVERT formats in books online... select CONVERT(char(8),datetime,112), count(*) as dt from sitelog where datetime >= '2007-02-03 00:00' group by CONVERT(char(8),datetime,112) order by CONVERT(char(8),datetime,112) desc Or use the Day, Month and Year functions. When you use the LEFT function as you did above, it is actually doing a CONVERT...just to the default format. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268773 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
