SQL Server:与inparameters年、周、工作日的日期。

I have searched and have yet to find this little helpful snippet.

我已经搜索并还没有找到这个有用的小片段。

I want to input...

我想输入…

• Year (2014)
• 年(2014年)
• Weeknumber (2)
• Weeknumber(2)
• Weekday (2 = Tuesday, in my case)
• 工作日(我的情况是2 =周二)

Expected result: 2014-01-07 (seventh of January)

预期结果:2014-01-07(1月7日)

And get the full date in return, anyone?

并得到完整的日期作为回报，有人知道吗?

EDIT: My server is SQL 2008

编辑:我的服务器是SQL 2008。

---

The finished code thanks to all!.

完成的代码感谢大家!

declare @year int = 2014
declare @weeknr int = 2
declare @daynroffset int = 2


SELECT 
  DATEADD(DAY,+ (@daynroffset-1),
    DATEADD(DAY,-DATEPART(DW,CAST('1/1/' + cast(@year as varchar) AS Date))+2,DATEADD(WK,@weeknr-    1,CAST('1/1/' + cast(@year as varchar) AS Date)))
  )

4 个解决方案

#1

CODE:

2012+:
DATEADD(DAY,-DATEPART(DW,DATEFROMPARTS("YEAR",1,1))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,DATEFROMPARTS(2014,1,1)))

2012+: DATEADD(DAY，-DATEPART(DW,DATEFROMPARTS(“YEAR”，1,1))+1+“DAY OF WEEK”，DATEADD(WK，“WEEK NUMBER”-1,DATEFROMPARTS(2014,1,1)))

2008+:
SELECT DATEADD(DAY,-DATEPART(DW,CAST(CONCAT('1/1/',"YEAR") AS Date))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,CAST(CONCAT('1/1/',"YEAR") AS Date)))

2008+:选择DATEADD(DAY，-DATEPART(DW,CAST(CONCAT('1/1/'，"YEAR")为日期)+1+"DAY OF WEEK"，DATEADD(WK，"WEEK NUMBER" 1,CAST(CONCAT('1/1/'，"YEAR")为日期))))

Simply substitue the values where necessary.

只需在必要时替换值。

This will work for any date.

这将适用于任何日期。

#2

declare @year int = 2014
declare @week int = 2
declare @day int = 2

declare @date datetime = cast(cast(@year as varchar(20)) + '-01-01' as datetime)
declare @offset int = datepart(dw, @date) - 1
set @date = dateadd(day, @day - @offset, dateadd(ww, @week - 1, @date))

print @date

#3

The other answers (so far) use SQL Server default mechanisms to determine week and day of week. In this case the current language setting determines the day of the week (through the @@DATEFIRST setting) and the DATEPART(wk uses Jan 1st as the fixed date contained in week 1.

其他的答案(到目前为止)使用SQL Server默认机制来确定周和周。在本例中，当前语言设置确定了星期的日期(通过@@DATEFIRST设置)和DATEPART(wk使用1月1日作为第1周中包含的固定日期)。

To get a deterministic answer independent of the language setting one can use the ISO 8601 week standard which starts a week on Mondays and where the first week always contains Jan 4th.

要得到一个独立于语言环境的确定性答案，你可以使用ISO 8601周标准，它在周一开始一周，第一周总是包含1月4日。

This code determines the date based on ISO weeks:

此代码根据ISO周数确定日期:

declare @year int = 2016
declare @isoweek int = 22
declare @isoday int = 2

-- ISO-WEEK 1 always contains 4th Jan, so let's use this as a base
declare @date datetime = cast(cast(@year as varchar(4)) + '-01-04T12:00:00' as datetime)

-- Offset the wanted DayOfWeek versus our base date
-- We also set DATEFIRST temporarily because it affects DayOfWeek
-- ISO-Weeks always start on Monday
declare @datefirst int = @@DATEFIRST
SET DATEFIRST 1
declare @offset int = datepart(dw, @date) - 1
SET DATEFIRST @datefirst

-- Add given day and week to basedate
set @date = dateadd(day, @isoday - 1 - @offset, dateadd(wk, @isoweek - 1, @date))

print @date

#4

This may have issues near the year boundary, but it works for the example data given. You may want to add further validations. I've broken down each step of the datetime manipulation into a new field, so you can see it being constructed

这可能在年边界附近有问题，但是它对所给出的示例数据有效。您可能需要添加进一步的验证。我已经将datetime操作的每个步骤分解为一个新字段，以便您可以看到它正在被构造

2008

2008年

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = CAST( @year AS VARCHAR(4) )
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) ) )

2012+

2012 +

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = DATEFROMPARTS(@Year, 1, 1)
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) ) )