Conver to ISO week to month date on SQL

Question

I've searched on almost every question but haven't come with what I need, so here it goes:

I need to convert a date in the format of 'yyyyww' (where 'ww' is the iso week of the year) to 'yyyymm' (where 'mm' is the month of the year).

So, for example, I have the date 201725 (which is year 2017, ISO week 25) to be displayed as 201706, since the ISO week 25 is on June of the same year.

Why do you want 201706? There are several weeks in June? – Sean Lange Oct 10 '17 at 14:48 — Sean Lange, Oct 10 '17 at 14:48
Basically, I want to 'group' the ISO weeks into months. – PabloRjs Oct 10 '17 at 15:02 — PabloRjs, Oct 10 '17 at 15:02

score 1 · Accepted Answer · answered Oct 10 '17 at 14:54

1

Use a combination of the LEFT, CONVERT & DATEADD Functions.

SELECT LEFT(CONVERT(varchar, DATEADD(week, yourfield % 100 - 1, DATEADD(year, yourfield / 100 - 1900, 0)),112),6)
FROM yourtable

Input

201722
201733
201725

Output

201705
201708
201706

SQL Fiddle: http://sqlfiddle.com/#!6/00eaa/7/0

answered Oct 10 '17 at 14:54

Matt

14,906
27
99
149

This is the best approach and the most elegant way I've seen!! Thanks for sharing and saving from tons of unnecessary code!!! – Natallia May 13 '19 at 16:49

score 0 · Answer 2 · answered Oct 10 '17 at 14:52

This is a bit strange for output because you can get the same value for more than 1 week but I will leave that up to you to sort out.

This produces the desired output stated for the input provided.

declare @YourValue varchar(10) = '201725'

select left(replace(convert(varchar(10), dateadd(week, convert(int, right(@YourValue, 2)), left(@YourValue, 4) + '0101'), 102), '.', ''), 6)

Conver to ISO week to month date on SQL

2 Answers2