使用像ISO_WEEK这样的值，但在星期日开始 [英] Use value like ISO_WEEK but start week on sunday

问题描述

我正在尝试使用我的日历维度来正确计算ISO_week似乎是一个解决方案的一年中的几周，但我在美国，我们的周从星期日开始。有没有办法在星期日开始几个星期，同时仍然在适当的几周内结束/开始几年，如ISO_WEEK如何计算它？我目前的添加到字典开始于星期日，但每年有53周，因为我没有深入的公式。目前我正在使用

I am trying to get my calendar dimension to properly calculate the weeks of the year which ISO_week seemed to be a solution yet I am in the USA and our weeks start on Sunday. Is there a way to get the weeks to start on Sundays while still having end/start of years being in proper weeks like how ISO_WEEK calculates it? My current Add to dictionary starts on Sundays yet has a 53rd week every year because I don't have an indepth formula. Currently I'm using

DATEPART(WW, @CurrentDate) AS week_of_year,

@CurrentDate的背景信息将是

Background information on @CurrentDate would be

DECLARE @StartDate DATETIME = '01/04/2010' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2050' --End Value of Date Range
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)

我尝试过： < br $>


目前对于ISOweek我有：

What I have tried:

Currently for ISOweek I have:

[isoweek] AS DATEPART(ISO_WEEK, [date_day]),

Date_day就是日期

我也试过

Date_day is just date
I have also tried

[iso_week_of_year] TINYINT NULL,
iso_week_of_year = CONVERT(TINYINT, isoweek),

这些只是按照预期返回周一开始的周数。

These just return the weeks starting on Monday as expected.

推荐答案

开始之前那么，你需要为周数定义你的规则 - ISO规范在星期一非常稳定地放下它们： ISO周日期 - 维基百科 [ ^ ]并严重依赖于本周星期四下降 - 因此ISO年份可能有364到371天，具体取决于日历周的第一个星期四 - 这意味着1月1日可能是在上一年的第52周或第53周，或今年的第1周。



你需要先准确计算出你的年份，并从那里开始！

Before you start doing that, you need to define your "rules" for Week Number - the ISO specification lays them down very solidly for Monday weeks: ISO week date - Wikipedia[^] and is heavily dependant on the week in which the Thursday falls - and as a result an "ISO year" can have between 364 and 371 days depending on the first Thursday of the "calendar" week - meaning that Jan 1st can be in Week 52 or 53 of the previous year, or week 1 of "this" year.

You would need to first work out exactly when your year is going to start, and go from there!

我的推荐将审查在添加ISO_Week以进行计算之前使用的一些SQL Server方法;然后根据稍微调整过的版本创建一个新函数。



我建议在Sql Server Central上查看这篇文章，看起来可能是使用Tally-Table，通常比其他版本的性能更高。

计算ISO周数的简单公式 - SQLServerCentral [ ^ ]

My recommendation would be to review some of the SQL Server methods that were used before ISO_Week was added to do the calculation; and then create a new function based on a slightly tweaked version of what's out there.

I would recommend reviewing this article over on Sql Server Central, looks like it may be working a Tally-Table which generally operates at a higher-performance than other variations.
A Simple Formula to Calculate the ISO Week Number - SQLServerCentral[^]

最终工作的结果非常简单我无法相信我之前没有看到它...

只是推动日期工作奇妙。仍然需要在很长一段时间后验证数据，但是星期几现在而不是星期一开始几周！

What ended up working was literally so simple I can't believe I didn't see it earlier...
Just pushing the date worked wonderfully. Still have to validate data after long periods of time but weeks are starting on Sundays now instead of Mondays!

DATEPART(ISO_WEEK, DATEADD(day,+1,@CurrentDate)) AS week_of_year

这篇关于使用像ISO_WEEK这样的值，但在星期日开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！