MS SQL full datetime to only month and date i am using ms sql server, i have date filed with value '01-Jan-2016 12:12:03PM'
from this format i need something like 'January-2016' 
how can i do this?
with this i need to count something by month.

MY code following

select
DATENAME(MONTH,p._DATE) AS AC_MONTH_,
COUNT(CASE WHEN p.PC=266 THEN 1 END) AS PRE_DATE,
COUNT(CASE WHEN p.PC=1325 THEN 1 END) PP_DATE
from true_all p
where  p._DATE&gt;'01-Jun-2016'
group by DATENAME(MONTH,p._DATE)
order by DATENAME(MONTH,p._DATE) desc
For best performance, use date/time arithmetic to truncate a datetime column to year and month before using it for grouping. There are at least two distinct approaches to that.

Approach 1

The first is a well-known date/time truncation method applicable to various units, not just months. It consists in finding the difference between your datetime value and another, fixed, datetime value in the same same units that you want to perform the truncation on and adding that difference to the fixed datetime value. Very often a 0 is used as an easily memorable fixed value (it represents the date of 1900-01-01 00:00):

DATEADD(unit, DATEDIFF(unit, 0, yourDateTimeValue), 0)


You want to do the truncation on months – so, in your case it would be

DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)


The result will be the datetime value representing the midnight of the first of the same month as p._DATE. For 2016-11-16 14:32:58.547, for instance, you will get 2016-11-01 00:00:00.000.

Approach 2

Another method, primarily applicable to dates but easily adaptable for datetimes, is to take the date's day value (for instance, 16 for today), decrease it by one (15) and subtract that number of days from the date. As you can understand, that will also give you the first of the same month. In your case the expression would be:

 DATEADD(DAY, 1 - DAY(p._DATE), p._DATE)


That assumes p._DATE is the date type. If it is datetime, just cast the second occurrence to date:

 DATEADD(DAY, 1 - DAY(P._DATE), CAST(P._DATE AS date))


Usage

With both approaches the expression is supposed to be used as the grouping criterion. Using the first one as an example, your query could be rewritten like this:

SELECT
  AC_MONTH_ = DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)         
FROM
  dbo.true_all AS p
WHERE
  p._DATE &gt; '01-Jun-2016'
GROUP BY
  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
ORDER BY
  AC_MONTH_ DESC
;


Notes on formatting

Of course, if you want the date values in the output to look like Month-YYYY, you will need to format them accordingly. Conventionally, formatting should be done at the presentation level, not in the database, so that the application could take advantage of the client-side locale settings.

If you insist on doing it in Transact-SQL, though, you can certainly have some options. Just make sure you apply the formatting to the already transformed value (the expression) rather than the source column. Use of the DATENAME function is one of the options:

  AC_MONTH_ = DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0))
            + '-'
            + DATENAME(YEAR,  DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)),


In order to suppress repetition of the same long expression, you can use CROSS APPLY to calculate the month date once and reference it both in GROUP BY and in SELECT, like this:

SELECT
  AC_MONTH_ = DATENAME(MONTH, x.MONTH_DATE) + '-' + DATENAME(MONTH, x.MONTH_DATE),
  PRE_DATE  = COUNT(CASE WHEN p.PC=266  THEN 1 END),
  PP_DATE   = COUNT(CASE WHEN p.PC=1325 THEN 1 END)         
FROM
  dbo.true_all AS p
  CROSS APPLY
  (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, p._DATE), 0)
  ) AS x (MONTH_DATE)
WHERE
  p._DATE &gt; '01-Jun-2016'
GROUP BY
  x.MONTH_DATE
ORDER BY
  x.MONTH_DATE DESC
;


Note the ORDER BY x.MONTH_DATE DESC in this case. Now that the AC_MONTH_ column holds formatted values, it can no longer be used for consistent sorting. But the grouping criterion can, so the above query is using it in ORDER BY.