This is how I'm doing it for now...
select count(*) as dt <cfif url.stats eq "bar-day">, left(datetime,11) as
datetime</cfif>
from sitelog
where datepart(year,datetime) = '#theyear#'
and datepart(month,datetime) = '#themonth#'
<cfif listLen(url.datestat,"/") eq 2>
and datepart(year,datetime) = '#listFirst(url.datestat,"/")#'
and datepart(month,datetime) = '#listLast(url.datestat,"/")#'
</cfif>
<cfif not url.blankuser>AND userID <> 0</cfif>
<cfif url.u>AND userID = #url.u#</cfif>
<cfif url.g>AND groupID = #url.g#</cfif>
<cfif url.ID>AND ID = #url.id#</cfif>
<cfif isDefined("url.script_name")>AND script_name = '#url.script_name#'</cfif>
<cfif url.stats eq "bar-day">
group by left(datetime,11)
order by left(datetime,11) desc
</cfif>
Michael
At 09:50 PM 2/5/2007, you wrote:
>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:268774
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
