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)

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

and a query of queries

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

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

5 thoughts on “MS SQL WHERE, GROUP BY and ORDER BY on Datetime”

  1. I’m trying to adapt what you’ve done to fix my issue – no I’m struggling. I am looking to write a query to show additions to a website from the last week all in one list on the main page.

    One part, for example, I’d like to show the recent company promotions. How do I write:

    select *
    from promotions
    where promodate > ????
    order by promodate desc

    Any ideas for me?
    Much appreciated!

  2. Allie,

    You’ll want to try something like this:

    Select *
    From Promotions
    Where promodate between getdate()-7 and getdate()

    The where clause on this should limit you to any rows where promodate is between now and seven days ago.


  3. What about casting to a datetime instead of having it converted to a string? Might eliminate the need for a query of query because it should order by the dates correctly

Leave a Reply

Your email address will not be published. Required fields are marked *