Date Sorting

[jorbroni]

Hello

I have written a script that will give me the sum amount of sales per day. Howevever, I need the summary group by date "dd/mm/yyyy". When I put the group by statment in my script and ran the query, it still gave me the individual transactions. The problem is in the date. The date is also including the time of day the transaction posted.

What can I do to make it group by date and not by datetime?

Thanks

 

[clarkin]

You want to still have a datetime value, but to drop the time bits (ie, set them to 00:00:00.000). There's several ways to do it but the way i've found to be most reliable is to double convert:

[tt]CONVERT(datetime,FLOOR(CONVERT(float,@someDatetime)))
4^ 3^ 2^ 1^[/tt]

1 - your original datetime with date & time
2 - convert to a decimal
3 - round down (eliminates everything but days)
4 - convert back to datetime (eg 2004-12-24 00:00:00.000)

So you could:

SELECT CONVERT(datetime,FLOOR(CONVERT(float,SaleDatetime))) AS Day, Sum(SaleValue) AS Sales FROM someTable GROUP BY CONVERT(datetime,FLOOR(CONVERT(float,SaleDatetime)))

see thread183-658085 for more info. Good luck

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].