SQL Server:使用带 ISOWK 参数的 DATEPART 将一周的第一天更改为星期日 [英] SQL Server: change first day of week to Sunday using DATEPART with ISOWK parameter

问题描述

我需要获取某些日期的周数.例如，对于 2016 年 1 月，它应该类似于:

I need to get week numbers for some set of dates. For example, for Jan 2016 it should be something like:

[Week Number]
53             <--- for dates from Jan 1 to Jan 2
1              <--- for dates from Jan 3 to Jan 9
2              <--- for dates from Jan 10 to Jan 16
...            <--- etc. 

如您所见，一周应该从星期日开始.我使用以下函数来设置开始日期:

As you see, week should start from Sunday. I've used following function to set start day:

SET DATEFIRST 7

这个是用来获取周数的:

And this one to get week number:

DATEPART(ISOWK, SOME_DATE)

但我注意到 DATEFIRST 在使用 ISOWK 时不会影响结果 - 周总是从星期一开始.

But I've noticed that DATEFIRST doesn't affect results when ISOWK being used - week always starts from Monday.

那么，我做错了什么?谢谢！

So, what am I doing wrong? Thank you!

UPD:

我做了一个函数，看起来效果不错:

I've made a function, seems it works good:

CREATE FUNCTION [dbo].[GetWeekNumber]
( 
    @date as DATETIME,
    @offset as INT
)
RETURNS NVARCHAR(100)
    BEGIN 
        DECLARE @weekNumber as int
        DECLARE @year as int

        SET @date = DATEADD(MINUTE, @offset, @date)
        SET @year = DATEPART(year, DATEADD(DAY, 1 - DATEPART(WEEKDAY, @date), CAST(@date AS DATE)))

        IF @@DATEFIRST = 7 
            BEGIN
                SET @date = DATEADD(day, 1, @date)
            END
        SET @weekNumber = DATEPART(ISO_WEEK, @date)
        RETURN concat(@year, ', Week ', right('0' + convert(nvarchar, @weekNumber), 2))
    END

您需要传递日期和时区偏移量(以分钟为单位).并且 SET DATEFIRST @startDay 在执行此函数之前(1 - 星期一，7 - 星期日).示例:

You need to pass the date and timezone offset (in minutes). And SET DATEFIRST @startDay before executing this function (1 - Monday, 7 - Sunday). Example:

DECLARE @date as DATETIME
SET @date = '2016-01-03 12:00'

SET DATEFIRST 1
SELECT dbo.GetWeekNumber(@date, 0) -- 2015, Week 53

SET DATEFIRST 7
SELECT dbo.GetWeekNumber(@date, 0) -- 2016, Week 01

推荐答案

这是设计使然.根据 MSDN:

This is by design. According to MSDN:

ISO 8601 包括 ISO 周日期系统，这是一个编号系统周.每个星期都与星期四发生的年份相关联.例如，2004 年的第 1 周 (2004W01) 从 2003 年 12 月 29 日星期一开始到 2004 年 1 月 4 日星期日.

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004.

所以 ISOWK 总是基于星期四，并且不受 DATEFIRST 的影响.

So ISOWK is always based on Thursday, and is not affected by DATEFIRST.

要获得您想要的结果，只需使用 WEEK 而不是 ISOWK.

To get the results you want, just use WEEK instead of ISOWK.

更新:同样来自 MSDN:

UPDATE: Also from MSDN:

任何一年的 1 月 1 日定义一周的起始数字datepart，例如:DATEPART (wk, 'Jan 1, xxxx') = 1，其中 xxxx 为任何一年.

January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

让第 1 周在 1 月 3 日至 9 日举行的唯一方法是使用数学.从 DATEPART(wk...) 值中减去一个，如果结果为 0，则将其设为 53(或者只需在您的日期使用 DATEADD 减去一周).没有更简单的方法，因为按照设计，任何一年的第一周都是包含 1 月 1 日的那一周.

The only way to get Week #1 to be Jan 3-9 is to use math. Subtract one from the DATEPART(wk...) value, and if the result is 0, make it 53 (or simply use DATEADD on your date to subtract a week). There is no simpler way, as by design, the first week of any year will be the week that contains Jan 1.

这篇关于SQL Server:使用带 ISOWK 参数的 DATEPART 将一周的第一天更改为星期日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！