This is my query:

SELECT distinct 'wk '   Trim (convert(varchar(max),DATEADD(Week, DATEDIFF(week, 0,  CONVERT(VARCHAR(max),period)), -1) ))[week]
        
FROM    ABC
GROUP BY Trim (convert(varchar(max),DATEADD(Week, DATEDIFF(week, 0, CONVERT(VARCHAR(max),period)), -1)))

This is giving me a format like wk Aug 7 2022 12:00AM but I want this format wk Aug 7 , 2022

CodePudding user response：

There was an error in the answer, it always returned january, the error is fixed now

You could build your string using the datepart function

declare @period datetime = '20220807' -- getdate()

SELECT 'wk '   
       left(datename(month, @period), 3)   
       ' '   convert(varchar(2), datepart(day, @period))   
       ' ,'   convert(varchar(4), datepart(year, @period))

looks like this wk Aug 7 ,2022

To convert your select clause to use this,

declare @table1 table (id int, period date)
insert into @table1 (id, period) values (1, '20220807'), (2, '20211130')

SELECT 'wk '   
       left(datename(month, dateadd( week, DATEDIFF( week, 0, CONVERT(VARCHAR(12), convert(date, period))), -1)), 3)   
       ' '   convert(varchar(2), datepart(day, dateadd( week, DATEDIFF( week, 0, CONVERT(VARCHAR(12), convert(date, period))), -1)))   
       ' ,'   convert(varchar(4), datepart(year, dateadd( week, DATEDIFF( week, 0, CONVERT(VARCHAR(12), convert(date, period))), -1)))
from   @table1

it looks like this