SQL Server 2005 中的isoweek [英] Isoweek in SQL Server 2005

问题描述

在 SQL Server 2008 中，可以通过以下方式找到 isoweek:

In SQL Server 2008 the isoweek can be found with this:

SELECT datepart(iso_week, getdate())

在 SQL Server 2008 之前，没有找到 isoweek 的内置函数.

Before SQL Server 2008 there were no built-in function to find isoweek.

我一直在寻找一个好的语法来为 SQL Server 2005 找到用户定义的 iso_week.我找到了很多解决方案.不喜欢我找到的任何解决方案，其中大部分都不起作用，而且它们太长了.

I have been searching high and low for a good syntax to find a userdefined iso_week for SQL Server 2005. I found quite a few solutions. Didn't like any of the solutions I found, most of them didn't work, and they were way too long.

由于这个问题很老，我希望这个问题已经耗尽并且已经找到了最好的解决方案.但是我找不到好的方法.

Since the issue is very old, I would expect this issue is depleted and the best solution has been found. I was unable to find a good method though.

我写了一个解决方案，稍后我会发布.但在此之前，我想绝对确保没有其他人可以匹配我编写的解决方案.

I wrote a solution, which I am going to post later. But before I do, I want to make absolutely sure that no one else can match the solution I wrote.

我希望获得自学者徽章.我敦促人们为这个古老的问题找到最好的答案.

I am hoping to earn the self-learner badge. I urge people to find the best answers out there for this ancient question.

在让人们有机会找到好的解决方案后，我将发布我的答案.

I am going to post my answer after giving people a chance of finding a good solution.

推荐答案

这里有一种与您类似的方法，它也依赖于本周的星期四.但最终它以不同的方式使用日期.

Here's an approach that is similar to yours in that it also relies on this week's Thursday. But in the end it uses the date differently.

1. 获取本 (ISO) 周星期四的日期.

1. Get the date of this (ISO) week's Thursday.

您自己的解决方案使用已知星期四的硬编码日期.或者，可以在 @@DATEFIRST 的帮助下找到本周的星期四:

Your own solution uses the hard coded date of a known Thursday. Alternatively, this week's Thursday could be found with the help of @@DATEFIRST:

SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)

(我并没有为正确的公式苦苦挣扎，因为它是 我已经知道.)

(I wasn't struggling too much for the right formula because it was already known to me.)

获取一年中的星期四:

SELECT DY = DATEPART(DAYOFYEAR, Th)

像这样使用数字找出一周:

Use the number to find out the week like this:

SELECT ISOWeek = (DY - 1) / 7 + 1

以下是单个语句中的上述计算:

Here are the above calculations in a single statement:

SELECT ISOWeek = (DATEPART(DAYOFYEAR, Th) - 1) / 7 + 1
FROM (
  SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)
) s;

这篇关于SQL Server 2005 中的isoweek的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！