Group date by week as YYYY/MM/WK

Question

I have made below query and its giving out put to show Year & Month. I need to add week number also

select CONVERT(VARCHAR(7), DATEADD(MONTH, DATEDIFF(MONTH, 0, tqr_date), 0), 111) AS [Period Date],
REPLACE(RIGHT(CONVERT(VARCHAR(11), DATEADD(MONTH, DATEDIFF(MONTH, 0, tqr_date), 0), 106), 8), ' ', '-') AS Period,

Output will be 2019/02, Feb-2019

Expected output 2019/02/5,(where 1 is week number), Week-05

Have a look at this answer - https://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008/ Looks like there is no inbuilt solution. — Sudipta Mondal, Apr 01 '19 at 05:17
Check this : https://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql Possible duplicate — Mayank Dudakiya, Apr 01 '19 at 05:17

score 1 · Accepted Answer · answered Apr 01 '19 at 05:21

you can try below - using concat DATEPART ( dw , getdate() )

select concat(CONVERT(VARCHAR(7), DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0), 111),'/',
DATEPART ( dw , getdate() ) )
AS [Period Date], 
REPLACE(RIGHT(CONVERT(VARCHAR(11),
DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0), 106), 8), ' ', '-') AS Period

score 0 · Answer 2 · answered Apr 01 '19 at 09:25

0

     select Convert(varchar(20),getdate(),111) AS [Period Date],  
 DATEPART(WEEK, getdate())  - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,getdate()), 0))+ 1 AS WEEK_OF_MONTH

answered Apr 01 '19 at 09:25

Vikram Singh

114
3

Group date by week as YYYY/MM/WK

2 Answers2