sql-server - SQL中两个日期之间的周末日期和日期

Question

我想在两个日期范围内获得所有日期和日期。像这样

Day     Dt
Tuesday 2020-12-01
Friday  2020-12-04
Tuesday 2020-12-08
Friday  2020-12-11
Tuesday 2020-12-15
Friday  2020-12-18
Tuesday 2020-12-22
Friday  2020-12-25
Tuesday 2020-12-29

我正在使用以下功能

ALTER FUNCTION [dbo].[fun_GetDatesforAday] (
    -- Add the parameters for the function here
    @fromD DATETIME
    ,@toD DATETIME
    
    ,@DayName VARCHAR(1000)
    )
RETURNS @table TABLE (
    --Id INT IDENTITY(1,1)
    [Day] VARCHAR(20)
    ,Dt DATE
    )
AS
BEGIN
    DECLARE @ServiceDays VARCHAR(1000)
    SET @ServiceDays = ''''+ REPLACE(@DayName,',',''',''')+''''

    
    while @fromD < = @toD
Begin
if datename (weekday, @fromD) in (@ServiceDays)
INSERT INTO @table
    VALUES (DATENAME(weekday, @fromD), @fromD)
 
SET @fromD = DATEADD(D, 1, @fromD)
END

    RETURN
END

SELECT  * FROM dbo.fun_GetDatesforAday('2020-12-01','2020-12-29','Tuesday,Friday')

注意：如果我使用这种条件，那么它的工作原理

if datename (weekday, @fromD) in ('Tuesday','Friday')

这些天我不能在我的函数中硬编码，我必须从函数调用中传递 days 参数。如果我的代码有任何错误，请帮助我。提前致谢

标签： sql-servertsql

Answer 1

您需要拆分最后一个参数的字符串。

如果你像这样改变你的功能：

ALTER FUNCTION [dbo].[fun_GetDatesforAday] (
    -- Add the parameters for the function here
    @fromD DATETIME
    ,@toD DATETIME
    
    ,@DayName VARCHAR(1000)
    )
RETURNS @table TABLE (
    --Id INT IDENTITY(1,1)
    [Day] VARCHAR(20)
    ,Dt DATE
    )
AS
BEGIN

    --DECLARE @ServiceDays VARCHAR(1000)
    --SET @ServiceDays = ''''+ REPLACE(@DayName,',',''',''')+''''
        
    
    while @fromD < = @toD
Begin
if datename (weekday, @fromD) in (SELECT Value FROM string_split(@DayName,','))
INSERT INTO @table
    VALUES (DATENAME(weekday, @fromD), @fromD)
 
SET @fromD = DATEADD(D, 1, @fromD)
END

    RETURN
END

然后它会工作。

使用SELECT * FROM dbo.fun_GetDatesforAday('2020-12-01','2020-12-29','Tuesday,Friday')

你会得到预期的结果：