Search code examples
sqlsql-serverdateisoweek-number

Conver to ISO week to month date on SQL

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.

Solution

  • 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