SQL Server Performance Forum – Threads Archive

Help with Dating

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.

---

]]>