Remove the time part in Datediff

talent in making 0
Viewed 37

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)))

I get a result like this: wk Aug 7 2022 12:00AM

But I want this format wk Aug 7, 2022

tsql sql-server
1 Answers

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

COLUMN1
wk Aug 7 ,2022
wk Nov 28 ,2021
GuidoG0
Related