[thelist] [TIP] SQL

[Joshua Olson]

<tip type="SQL" author="Joshua Olson">
This tip is specifically for MS SQL Server.  If you'd like to group all the
records for a particular day, you can do some fancy footwork with date part,
or you could do something like this:

SELECT 
    Convert(varchar, [date], 111) AS [date]
  , [agg_columnlist]
GROUP BY Convert(datetime, Convert(varchar, [date], 111), 111) AS [date]

The first column now contains the date formatted as "YYYY/MM/DD", casted as
a string.  If you want the results in date order, you need only sort by the
first column.  So, add "ORDER BY 1" or "ORDER BY 1 DESC"

If you want back a real date object in the results, change the query to
this:

SELECT 
    Convert(datetime, Convert(varchar, [date], 111), 111) AS [date]
  , [agg_columnlist]
GROUP BY Convert(datetime, Convert(varchar, [date], 111), 111) AS [date]
</tip>

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/