基于周数和日数字段设置SQL日期字段值

Question

是否有基于周值和日值设置日期值的过程或方法。

要进一步详细说明使用此日历年：1月1日-7日为第1周，1月8日至14日是第2周等

对于Day数值；星期一是1星期二是2星期三是3星期四是4点星期五是5点。

使用这个逻辑，我希望创建并填充一个使用这些值的日期字段。

例如，我希望第2周-第3天将我的新日期字段填充为2018年1月10日

如有任何帮助和指导，我们将不胜感激。

Answer 1

有些事情可以通过datetime函数来完成，只根据周数和工作日数来计算日期。

但是，请注意@@DATEFIRST在服务器上可能不同
定义哪个工作日是一周中的第一天
所以最好在你的课程中将其更改为1，使周一成为第一个工作日
如果没有它，下面使用的计算可能需要进行调整
例如[Weekday]+1，当select @@DATEFIRST返回7 时

示例片段：

declare @Table table ([Week] int, [Weekday] int);
insert into @Table ([Week], [Weekday]) values
(1, 1)
,(1, 7)
,(2, 1)
,(2, 7)
;
-- The US default for @@DATEFIRST is 7, which would make Sunday weekday 1. 
-- By setting @@DATEFIRST to 1 then Monday will be weekday 1
SET DATEFIRST 1; 
SELECT [Week], [Weekday],
DATEADD(WEEKDAY, 1-DATEPART(WEEKDAY, DATEFROMPARTS(DATEPART(YEAR,GetDate()),1,1)), DATEADD(WEEK,[Week]-1, DATEFROMPARTS(DATEPART(YEAR,GetDate()),1,[Weekday]))) AS CalcDate
FROM @Table
ORDER BY [Week], [Weekday];

结果：

Week Weekday CalcDate
---- ------- ----------
1       1 2018-01-01
1       7 2018-01-07
2       1 2018-01-08
2       7 2018-01-14

请注意，年份以当前日期为基础。

但是，如果是更新现有的表
那么你可以换个角度来看待它
创建一个包含当前年份中所有日期的临时表，并导出周&从这些日期算起的工作日
然后您可以使用该临时表来更新现有表。

示例片段：

-- Create a temporary table
IF OBJECT_ID('tempdb..#tmpDates') IS NOT NULL DROP TABLE #tmpDates;
CREATE TABLE #tmpDates ([Week] INT NOT NULL, [WeekDay] INT NOT NULL, [Date] DATE NOT NULL, PRIMARY KEY ([Week], [WeekDay]));
-- Make Monday the first weekday
SET DATEFIRST 1;
-- Filling the temp table with data
WITH DATES2018 AS
(
SELECT CAST('2018-01-01' AS DATE) AS [Date]
UNION ALL
SELECT DATEADD(day, 1, [Date])
FROM DATES2018
WHERE [Date] < CAST('2018-12-31' AS DATE)
)
INSERT INTO #tmpDates ([Date], [Week], [WeekDay])
SELECT [Date], DATEPART(WEEK, [Date]) AS [Week], DATEPART(WEEKDAY, [Date]) as [WeekDay]
FROM DATES2018
ORDER BY [Week], [WeekDay]
OPTION (MAXRECURSION 366);

-- Just using a table variable per demonstration
declare @YourTable table ([Week] int, [Weekday] int, [Date] DATE);
-- Sample data
insert into @YourTable ([Week], [Weekday]) values
(1, 1)
,(1, 7)
,(2, 1)
,(2, 7)
;
-- update based on the temp table
update t
set [Date] = tmp.[Date]
from @YourTable t 
join #tmpDates tmp on (tmp.[Week] = t.[Week] AND tmp.[WeekDay] = t.[Weekday])
where (t.[Date] is null OR t.[Date] <> tmp.[Date]);
select * from @YourTable;

Answer 2

您可以参考这个链接，它有一个日历表创建语句https://www.sqlshack.com/designing-a-calendar-table/

你需要做的是将你的可用列(如年、周和日(映射到日历表中，并提取日期

Answer 3

也许这样的东西可以让你开始
它会从你的样品中返回日期，但我没有在所有情况下进行测试
所以我相信它需要一些微调，使用@Ajan Balakumaran建议的Calender Table会更好

declare @year int = datepart(year, getdate())
declare @month int
declare @week int = 2
declare @day int = 3
SELECT @month = DATEPART(MM,CAST(CONVERT(CHAR(3),
DATEADD(WW, @WEEK - 1,
CONVERT(datetime, '01/01/' + CONVERT(char(4) ,@Year)))
, 100) + ' 1900' AS DATETIME))
select datefromparts(2018, @month, @day + (@week - 1) * 7)