I decided to implement login tracking for one of the personal applications I’m developing. Getting each users login time stamp into the database is the easy part, however building graphs and reports is a bit more difficult. This has to do with the way MS SQL stores dates, they always have a time stamp. If you don’t specify one when you insert your date it will add 12:00:00 AM for you, so searching for dates like this is easy. However, my login tracking actually inserts the time of login as well.
So, I wanted to create a graph that showed me how many total users had logged in over the past week. So, I started with some code like this:

<cfquery name="Getlastseven" datasource="#request.dsn#">
select left(login_date, 11) as date2, count(*) as Totals
from loginhistory
Where Login_Date >= '#Date7#' 
AND login_date < '#CurrDate1#'
group by left(login_date, 11)
order by left(login_date, 11)
</cfquery>

where Date7 is the current date minus seven days and currdate1 is the current date plus one (to account for the way SQL Server handles times that are close to the next day). However, this gave me dates arranged alphabetically, so August came before July, not what I wanted.
So, to fix, I broke it out into a main query:

<cfquery name="GetLast" datasource="#request.dsn#">
select convert(varchar,login_date,101) as date2
from loginhistory 
Where Login_Date >= '#Date7#' 
AND login_date < '#CurrDate1#' 
order by convert(varchar,login_date,101) ASC
</cfquery>

and a query of queries

<cfquery name="CountLogins" dbtype="query">
Select date2, count(*) as Totals
From getlast
Group By date2
</cfquery>

which does exactly what I want and now my cfchart isn’t ordered all weird.