SQL Get "Iso Year" for Iso Week

SQL Get ISO Year for ISO Week

~~This solution~~ The code in the question does not return the correct value for the date '1-1-2027'.

The following will return the correct value with all dates i tested (and i tested quite a few).

SELECT YEAR(DATEADD(day, 26 - DATEPART(isoww, '2012-01-01'), '2012-01-01'))

As taken from: https://capens.net/content/sql-year-iso-week

SQL - How to get the Year-Week using the ISO weeks?

Use the CONCAT function. + is both the addition operator and concatenation operator in T-SQL; which is it inferred to be depends on the data that surrounds it. Here it is being inferred as a plus operator, not a concatenation operator, as the data in the query are int values, and the '-' is therefore being implicitly converted to an int too ('-' as an int is 0).

If we use the current date we have the expression below:

(YEAR(DATEADD(day, 26 - DATEPART(isoww, '20211028'), '20211028'))) + '-' + (datepart(ISOWK, '20211028'))

Which returns 2064. That's because it resolves to 2021 + '-' + 43 = 2021 + 0 + 43 = 2064.

If you use CONCAT you get what you want, which implicitly converts each value to a string based data type first:

SELECT CONCAT((YEAR(DATEADD(day, 26 - DATEPART(ISOWEEK, '20211028'), '20211028'))), '-', (DATEPART(ISOWEEK, '20211028')));

I also make your casing consistent and use the same keyword for the date part, ISOWEEK, as you you inconsistently use isoww and ISOWK.

How to get Year/Week according to ISO?

Use IYYY (ISO-year) rather than the YYYY (calendar year) format model.

SELECT TO_CHAR( DATE '2021-01-04' - 3, 'IYYYIW') AS yearweek
FROM   DUAL

Which outputs:

YEARWEEK
202053

SQLServer - Between two dates, generate table of Year, ISO Week, and Start and End dates

Of course, it's quite stupid to support ISO_WEEK, but no ISO_YEAR, only the combination YEAR/WEEK is usefull.

This modifies your existing query calculating the thursday of an ISO week, based on the definition of the ISO calendar each thursday is always in the correct year:

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     D.thursday) AS report_year,
           DATEPART(ISO_WEEK, D.thursday) AS report_week,
           MIN(report_date)               AS week_beginning_date,
           MAX(report_date)               AS week_ending_date
    FROM report_dates 
                      -- get thursday of week
      CROSS APPLY(VALUES(CAST(DATEADD(DAY, (DATEDIFF(DAY, 0, report_date) / 7) * 7, 3) AS DATE))) AS D(thursday)
  GROUP BY D.thursday
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

See fiddle

Btw, this is a perfect use case for a calendar table. Instead of repeating the same calculations over and over again(the week of a date will never change), you do it once. You can add any date calculation you ever need as a new column and no user has to think about the correct algorithm.

Edit:

If you want to get full weeks you can do begin/end of week based on the thursday and then you don't need t other days of the week:

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           DATEADD(DAY, -3,   report_date) AS week_beginning_date,
           DATEADD(DAY,  3,   report_date) AS week_ending_date
    FROM report_dates 
    WHERE report_date = DATEADD(DAY, (DATEDIFF(DAY, 0, report_date) / 7) * 7, 3) 
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

Fiddle

This could be further simplified to get only one the thursday per week in the initial select :-)

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211030';

;WITH report_dates AS (
        SELECT 
           TOP ((DATEDIFF(DAY, @windowStart, @windowEnd))/7 +1) 
               -- start on a thursday 
               report_date = DATEADD(WEEK, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     DATEADD(DAY, (DATEDIFF(DAY, 0, @windowStart) / 7) * 7, 3)) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           DATEADD(DAY, -3,   report_date) AS week_beginning_date,
           DATEADD(DAY,  3,   report_date) AS week_ending_date
    FROM report_dates 
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

fiddle

Selecting week / ISO week number from a date/time field

The DATEPART function expects a date / datetime / datetime2 value. You are passing in an integer representing the day or month number.

Assuming you're storing your dates correctly, you just need to pass in the date value directly:

DATEPART(WEEK, Max(AuditHistory.ActionedDateTime)) As WeekNo

What is ISO_year in sql-server

Here is a Connect item that requests a function to calculate ISO_YEAR.

DATEPART - ISO_YEAR for ISO_WEEK

In the workaround section you have this function that you can use.

CREATE FUNCTION [dbo].[ISOyear](@date DATETIME)
returns SMALLINT
AS
BEGIN
     DECLARE @isoyear SMALLINT = CASE
         WHEN Datepart(isowk, @date)=1
             AND Month(@date)=12 THEN Year(@date)+1
         WHEN Datepart(isowk, @date)=53
             AND Month(@date)=1 THEN Year(@date)-1
         WHEN Datepart(isowk, @date)=52
             AND Month(@date)=1 THEN Year(@date)-1             
         ELSE Year(@date)
        END;
     RETURN @isoyear;
END;

Convert date to ISO week date in Spark

Spark SQL extract makes this much easier.

iso_year = F.expr("EXTRACT(YEAROFWEEK FROM my_date)")
iso_weekday = F.expr("EXTRACT(DAYOFWEEK_ISO FROM my_date)")

So, building off of the other answers with the use of concat_ws:

import pyspark.sql.functions as F

df.withColumn(
    'iso_week_date',
    F.concat_ws(
        "-",
        F.expr("EXTRACT(YEAROFWEEK FROM my_date)"),
        F.lpad(F.weekofyear('my_date'), 3, "W0"),
        F.expr("EXTRACT(DAYOFWEEK_ISO FROM my_date)")
    )
).show()

#+----------+-------------+
#|   my_date|iso_week_date|
#+----------+-------------+
#|1977-12-31|   1977-W52-6|
#|1978-01-01|   1977-W52-7|
#|1978-01-02|   1978-W01-1|
#|1978-12-31|   1978-W52-7|
#|1979-01-01|   1979-W01-1|
#|1979-12-30|   1979-W52-7|
#|1979-12-31|   1980-W01-1|
#|1980-01-01|   1980-W01-2|
#+----------+-------------+

Get Date from ISO Week in SQL

Change the -1 in your return to calculate if the first week of the year should be consider the first one or not (if have more than 3 days).
Something like this

case when DATEDIFF ( day ,  convert(datetime,'01/01/'+ @YearNum),@FirstDay )>=3 then 1 else 0 end

The full code, including the first sunday, can be improved, but works...

CREATE FUNCTION [dbo].[GetDateFromISOweek] (@Input VARCHAR(10))  
RETURNS DATETIME  
WITH EXECUTE AS CALLER  
AS  
BEGIN  
    DECLARE @YearNum CHAR(4) 
    DECLARE @WeekNum VARCHAR(2)
    declare @FirstDay datetime

    SET @YearNum = cast(SUBSTRING(@Input,0,CHARINDEX('W',@Input,0)) as int)+2000
    SET @WeekNum = SUBSTRING(@Input,CHARINDEX('W',@Input,0)+1,LEN(@Input))
    set @FirstDay=DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, DATEADD(YEAR, @YearNum - 1900, 0)) +  (8 - @@DATEFIRST) * 2) % 7, DATEADD(YEAR, @YearNum - 1900, 0))-1

    RETURN(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-case when DATEDIFF ( day ,  convert(datetime,'01/01/'+ @YearNum),@FirstDay )>=3 then 1 else 0 end), 7));
END; 
go
SET DATEFIRST 1; 
SELECT dbo.[GetDateFromISOweek]('15W52'),'15W52' union
SELECT dbo.[GetDateFromISOweek]('15W53'),'15W53' union
SELECT dbo.[GetDateFromISOweek]('16W01'), '16W01' union
SELECT dbo.[GetDateFromISOweek]('16W02'), '16W02' union
SELECT dbo.[GetDateFromISOweek]('16W03'), '16W03'

The result will be

----------------------- -----
2015-12-21 00:00:00.000 15W52
2015-12-28 00:00:00.000 15W53
2016-01-04 00:00:00.000 16W01
2016-01-11 00:00:00.000 16W02
2016-01-18 00:00:00.000 16W03

SQL Get "Iso Year" for Iso Week