如何在SQL Server中将日期正确转换为ISO-8601星期数字格式？ [英] How to properly convert a date into a ISO-8601 Week number format in SQL Server?

问题描述

ISO-8601声明周号的格式应为 YYYY-W ## -注意周号应为两位数字，如01、02，..

ISO-8601 states that week numbers are to be formated as YYYY-W## - observe that the week number should be two digits as 01, 02, ...

SELECT cast(DATEPART(YYYY, CreationDate) as varchar) + '-W' + 
       cast(DATEPART(ISO_WEEK, GETDATE())`

The问题是，这使周数为1、2，...

The problem is that this gives the week number as 1, 2, ...

提取2020-W01的正确方法是什么，...

What is the correct way of extracting 2020-W01, ...

推荐答案

原始问题文本与ISO_WEEK编号的一个简单格式问题有关，但是我认为与今年的串联有关的问题更大简单地将DatePart YYYY和ISO_WEEK串联会在第1周和/或第53周的几天中产生错误的（或至少是意外的）结果，尤其是像2014-12-31这样的日期是第1周的一部分，而不是2014-W01。同样，2016-01-01将成为2015-W53的一部分，而不是2016-W53。为了确定Iso Yea r-Week年份必须更正以考虑到这一点：

The original question text relates to a simple formatting issue of the ISO_WEEK number, but I feel there is a bigger issue here with the concatenation of the year part. Simply concatenating DatePart YYYY and ISO_WEEK will yield incorrect (or at least unexpected) results for days in week 1 and/or 53. Notably dates like 2014-12-31 are part of week 1, but not 2014-W01. It is part of 2015-W01. Similarly, 2016-01-01 will be part of 2015-W53, not 2016-W53. In order to determine the Iso Year-Week the year must be corrected to take this into account:

With
    Dates (Date) As (
            Select Convert( date, N'2014-12-31' )
Union All   Select Convert( date, N'2015-01-01' )
Union All   Select Convert( date, N'2015-12-31' )
Union All   Select Convert( date, N'2016-01-01' )
    )
Select
    src.Date
,   Concat( Case
        When DatePart( Month, src.Date ) = 12 And DatePart( ISO_WEEK, src.Date ) = 1 Then DatePart( Year, src.Date ) + 1
        When DatePart( Month, src.Date ) = 1 And DatePart( ISO_WEEK, src.Date ) > 50 Then DatePart( Year, src.Date ) - 1
        Else DatePart( Year, src.Date )
    End, N'-W', Right( Concat( N'00', DatePart( ISO_WEEK, src.Date ) ), 2 ) ) As IsoYearWeek
From
    Dates src
;

这篇关于如何在SQL Server中将日期正确转换为ISO-8601星期数字格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！