T-SQL - 根据周数获取开始和结束日期。

Our business considers a week from (Monday - Sunday). I need to write a T-SQL function, which passes in year, week no as parameters and it will return the start and end date of that week. However I've seen many examples but the problem lies within the year overlapping.

我们的业务考虑从(周一至周日)一周。我需要编写一个T-SQL函数,该函数以年份为单位传递,周数为参数,它将返回该周的开始和结束日期。不过我看过很多例子,但问题在于年内重叠。

e.g December 26, 2011 (Monday) - January 01, 2012 (Sunday)... << Would want to consider this as the last week of 2011.

例如,2011年12月26日(星期一) - 2012年1月1日(星期日)......“我想将此视为2011年的最后一周。

And also in T-SQL the datepart(ww,DATE) considers Sunday as the start of the week??

而且在T-SQL中,datepart(ww,DATE)将星期日视为一周的开始?

Or Am I better creating my own table with the week no and storing its start and end date?

或者我是否更好地创建我自己的表,其中包含no周并存储其开始和结束日期?

4 个解决方案

#1

DECLARE
    @Year INT,
    @Week INT,
    @FirstDayOfYear DATETIME,
    @FirstMondayOfYear DATETIME,
    @StartDate DATETIME,
    @EndDate DATETIME

SET @Year = 2011
SET @Week = 52

-- Get the first day of the provided year.
SET @FirstDayOfYear = CAST('1/1/' + CAST(@YEAR AS VARCHAR) AS DATETIME)

-- Get the first monday of the year, then add the number of weeks.
SET @FirstMondayOfYear = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, @FirstDayOfYear), @FirstDayOfYear)), 0)

SET @StartDate = DATEADD(WEEK, @Week - 1, @FirstMondayOfYear)

-- Set the end date to one week past the start date.
SET @EndDate = DATEADD(WEEK, 1, @StartDate)

SELECT @StartDate AS StartDate, DATEADD(SECOND, -1, @EndDate) AS EndDate

#2

You should create a table with the holidays and days you don´t wnat to consider in your counts.

您应该创建一个表格,其中包含假期和您不想在计算中考虑的日期。

After this count the days between the initial date and final date. (Step1)

在此之后计算初始日期和最终日期之间的天数。 (步骤1)

Select in your table to check how many days are between your ini and final dates. (Step2)

在表格中选择,检查您的ini和最终日期之间的天数。 (第2步)

Finally subtract the result of step 2 with result of step 1;

最后用步骤1的结果减去步骤2的结果;

#3

This is the sort of thing where you're better off creating a calendar table: the main issue is knowing how far into the past/future to populate it, but beyond that, have the table schema include week number and date. Depending on what other date-based queries you want to do, you might want to have additional column to break the date down into its constituant parts (e.g., have three separate columns for day/month/year/name of day, etc).

这就是你最好创建一个日历表的方法:主要问题是知道填充它的过去/未来有多远,但除此之外,让表模式包括周数和日期。根据您要执行的其他基于日期的查询,您可能希望有其他列将日期分解为其组成部分(例如,有三个单独的列,用于日/月/年/日期名称等)。

#4

How about this one:

这个怎么样:

--DROP FUNCTION dbo.GetBusinessWeekStart
CREATE FUNCTION dbo.GetBusinessWeekStart(
    @Year SMALLINT,
    @Week TINYINT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @FirstMonday TINYINT
    DECLARE @Result DATETIME

    IF ISNULL(@Week,0)<1 OR ISNULL(@Year,0)<1900
        BEGIN
            SET @Result= NULL;
        END
    ELSE
        BEGIN
            SET @FirstMonday=1

            WHILE DATEPART(dw,CONVERT(DATETIME, '01/0' + CONVERT(VARCHAR,@FirstMonday) + '/' + CONVERT(VARCHAR,@Year)))<>2
            BEGIN
                SET @FirstMonday=@FirstMonday+1
            END

            SET @Result=CONVERT(DATETIME, '01/0' + CONVERT(VARCHAR,@FirstMonday) + '/' + CONVERT(VARCHAR,@Year))
            SET @Result=DATEADD(d,(@Week-1)*7,@Result)

            IF DATEPART(yyyy,@Result)<>@Year
                BEGIN
                    SET @Result= NULL;
                END
        END

    RETURN @Result
END
GO

--Example
SELECT dbo.GetBusinessWeekStart(2011,15) [Start],dbo.GetBusinessWeekStart(2011,15)+6 [End]

#1

DECLARE
    @Year INT,
    @Week INT,
    @FirstDayOfYear DATETIME,
    @FirstMondayOfYear DATETIME,
    @StartDate DATETIME,
    @EndDate DATETIME

SET @Year = 2011
SET @Week = 52

-- Get the first day of the provided year.
SET @FirstDayOfYear = CAST('1/1/' + CAST(@YEAR AS VARCHAR) AS DATETIME)

-- Get the first monday of the year, then add the number of weeks.
SET @FirstMondayOfYear = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, @FirstDayOfYear), @FirstDayOfYear)), 0)

SET @StartDate = DATEADD(WEEK, @Week - 1, @FirstMondayOfYear)

-- Set the end date to one week past the start date.
SET @EndDate = DATEADD(WEEK, 1, @StartDate)

SELECT @StartDate AS StartDate, DATEADD(SECOND, -1, @EndDate) AS EndDate

#2