sql server - SQL: Display "01 Mar 2017" instead of "2017-03-01" ISSUE WITH GROUP -

i have code below works fine, i change shape of dates "01 mar 2017" instead of "2017-03-01"

select      *  (        select         [messagetype].[name],         case when ( format([occuredatutc], 'yyyy-mm-dd') '2017-03%' ) format([occuredatutc], 'yyyy-mm-dd')              else null                                                                        end [time],         count(*) [count]     @table     inner join @table on     group format([occuredatutc], 'yyyy-mm-dd'),              [messagetype].[name] ) s ( [time] not null ) order [time] asc 

outuput :

name_______time______count____ http    2017-03-01    21 http    2017-03-02    37 http    2017-03-03    42 . . http    2017-03-31    29 

• but if use convert(varchar(11), [occuredatutc], 106) after then in code, there problem grouping don't dates counted right anymore

what happens if try use convert(varchar(11), [occuredatutc], 106) in code:

// changed comented... 2 things

select      *  (        select         [messagetype].[name],         case when ( format([occuredatutc], 'yyyy-mm-dd') '2017-03%' ) convert(varchar(11), [occuredatutc], 106)     --format([occuredatutc], 'yyyy-mm-dd')              else null                                                                        end [time],         count(*) [count]     @table      inner join @table on     group [occuredatutc],    --format([occuredatutc], 'yyyy-mm-dd')              [messagetype].[name] ) s ( [time] not null ) order [time] asc 

i non grouped output:

http    01 mar 2017 1 http    01 mar 2017 1 http    01 mar 2017 1 http    01 mar 2017 2 http    01 mar 2017 1 http    01 mar 2017 1 . . http    02 mar 2017 1 . . http    31 mar 2017 1 

guess there problem data type

or group [occuredatutc]... (5 lines end) ... there jump error if use whole "convert(varchar(11), [occuredatutc], 106)" in group too

is there solution this? need output of dates in format "01 mar 2017" ...

original dates in [occuredatutc]:

2017-03-01 12:16:58.5080000 2017-03-01 18:11:53.3090000 2017-03-01 18:34:18.3090000 2017-03-01 20:42:28.8570000 2017-03-01 21:10:36.7070000 . . . 

thank in advance !!!

your query seems needlessly complicated. think want:

select [messagetype].[name],        convert(varchar(11), [occuredatutc], 106) [time],        count(*) [count] @table t inner join  -- assume these table names not real name      @table t2      on . . . occuredatutc >= '2017-03-01' ,       occuredatutc < '2017-04-01' group (convert(varchar(11), [occuredatutc], 106),          [messagetype].[name] 

notes:

• you don't need subquery.
• the from clause overly simplified; assume table names not both @table.
• use where clause filter before aggregation. more efficient filtering after aggregation.
• direct comparisons on date more efficient using function call on column (an index can used filter rows).