Remove the time part in Datediff

Question

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

Never use `varchar(max)` unless you know you are dealing with 8+mb strings - unlikely with dates; You probably don't need to cast your *period* column at all, and what do you think *group by* is doing for you here, given you're selecting *distinct* values? — Stu, Sep 10 '22 at 08:28
@Stu i got your point, thanks but how do i add the comma(,) in between wk Aug 7 and 2022 ? do you have any idea ? — talent in making, Sep 10 '22 at 08:41
Honestly, it seems like you should be passing the `date` to the application layer, and then controlling the *format* in the presentation layer. — Thom A, Sep 10 '22 at 09:22

score 2 · Accepted Answer · edited Sep 10 '22 at 09:19

2

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

edited Sep 10 '22 at 09:19

Thom A

88,727
11
45
75

answered Sep 10 '22 at 08:43

GuidoG

11,359
6
44
79

There was an error in the answer, it is fixed now – GuidoG Sep 10 '22 at 08:59
yeah but how do i convert my above query to act like this, moreover this query is giving wrong month sir – talent in making Sep 10 '22 at 09:00
@talentinmaking The error with the month is fixed – GuidoG Sep 10 '22 at 09:01
@talentinmaking I made a sample table to show how you can use this in your query – GuidoG Sep 10 '22 at 09:18
But why do you do this complex calculation of the date ? It seems it always creates the same data again where it started with, so why the calculation ? – GuidoG Sep 10 '22 at 09:20
I need the start date of the week only that's why i had to use that otherwise it was giving me the end date of the week – talent in making Sep 10 '22 at 09:23
1

Then look at [this accepted answer](https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) – GuidoG Sep 10 '22 at 09:27

Remove the time part in Datediff

1 Answers1