Group by Date

All date fields in SQL Server include the time. When you use Group By, the values must match exactly to be grouped, so a date field doesn't work very well fro grouping as you have seen as you end up with a group for each second or millisecond...

To fix this you need to CONVERT the date into a string that only includes the date...

Enter this in the "Formula" line of the Column definition for your new column:

Formula = "CONVERT(datetime, CONVERT(varchar(10), DateCreated, 101), 101)"

This is going to convert the DateCreated to a text (varchar) value striping off the time, then convert it back to a datetime with a default time of

Hope that helps...

Hi Holly here is an example SQL I wrote that illustrates grouping by date. It looks mean, but it really isnt. Essentially it extracts the day, month and year from the stored datetime field and then splices the extracted values into a string with "/" (forward slash) seperators to form something like d/mm/yyyy. It then calls the spliced date "TransactionDate". Note the comments symbolised by --

select sum(Total) as 'TotalSales' , -- unimportant for your purposes

-- This is the line that extracts the date as a string

-- not the year extraction is 4 numbers long so leave 4 spaces in the varchar for it else it will fail:: varchar(4)

cast( cast(day(Time) as varchar(2))+'/'+ cast(month(Time) as varchar(2))+'/'+ cast(year(Time) as varchar(4)) as datetime) as TransactionDate

into #Tdate -- unimportant for your purposes from [Transaction] -- unimportant for your purposes where 1=1 -- unimportant for your purposes

-- group the date string exactly as extracted

-- same as the above cast excpet on 1 line group by cast( cast(day(Time) as varchar(2)) +'/'+ cast(month(Time) as varchar(2)) +'/'+ cast(year(Time) as varchar(4)) as datetime)

Hope this helps you. Dan

Thanks guys, this worked out perfectly!

"Glenn Adams [MVP - Retail Mgmt]" wrote in message news: snipped-for-privacy@TK2MSFTNGP15.phx.gbl...

That's a keeper! (g) Very handy.

"Glenn Adams [MVP - Retail Mgmt]" wrote in message news: snipped-for-privacy@TK2MSFTNGP15.phx.gbl...