I am working on updating the Shipping Log report. I've added the Date Created field, and I would like to group the report by that field, so that I can get a total of shipping charges collected by day each month. However, when I arrange the report to do that, the date field expands to include the timestamp, so each shipping record is on its own line. Am I overlooking an easy way to tell the report to exclude the timestamps and group by day only? Any advice would be appreciated.
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
00:00:00 so that all of the date filters will still work as they do currently ...
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)
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.