I am trying to group by date (month/year) but the data I am querying is in month/day/year/time. I changed the format of that cell to MM/yyyy and it displays the date in the desired result. But it does not group them, I assume because the grouping is being applied prior to the format change. So none of the dates are common becase of the time. So how do I do this? Can I do it in the query, I thought about LEFT but I still don't want the day which is in the middle of the 2 pieces of info I need so that won't work.
Try This code, this may help u. Select Cast(Month(Dat) As Varchar(2))+'/'+ Cast(Year(Dat) As Varchar(4)) , Sum(Cnt) From Tst Group By Cast(Month(Dat) As Varchar(2))+'/'+ Cast(Year(Dat) As Varchar(4)) Thanks Sandy
This Can also solve your purpose. Select SUBSTRING(CONVERT(VARCHAR(20),DAT,105),4,10) As MMYY, Sum(Cnt) From Tst Group By SUBSTRING(CONVERT(VARCHAR(20),DAT,105),4,10) Sandy (DB Developer)
How about just grouping by year and month? use pubs select year(ord_date), month(ord_date), count(*) from sales group by year(ord_date), month(ord_date) -rl
And if you dont mind it in yyyymm format, I prefer convert(char(6),order_date,112) works great for order by and can always make it back into a date field format if needed later.
