title: SQL Server query to get total against each month of a year and return the year and month in date format not the string format
body: <p>I have a situation in which i have to calculate "Total" for each month of a year and i have to show total for each month of the year while maintaining datetime format of the date.i am writing the following query:</p>
  
  <pre><code>SELECT [DATE_FORMATION], sum(CO) CO, sum(Total_Members) Total_Members
  from (
      select  convert(varchar(7),CAST([DATE_FORMATION] AS DATE),126) as [DATE_FORMATION],
      count([CO_ID]) as CO,
      sum(convert(int,[TOTAL_MEMBERS])) as Total_Members
      from COCores
      where cast([DATE_FORMATION] as date) &gt;= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -10, current_timestamp)), 0)
      group by [DATE_FORMATION]
  ) tmp
  group by [DATE_FORMATION]
  </code></pre>
  
  <p>It is giving the accurate answer which i required</p>
  
  <p><a href="https://i.stack.imgur.com/xwtr2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xwtr2.png" alt="enter image description here"></a></p>
  
  <p>but the problem is it generates date in "Varchar" format where as i require it in datetime format so that i can use it further.how can i get this result while keeping the date in datetime format and getting the same result.</p>