从日期开始查询日期名称.但是从程序上看，我把日期搞错了，所以请帮助我

Question

这是我的SQL Server代码:

CREATE PROCEDURE [dbo].[usp_GetLeaveDateBewteenStartAndEndDate]      
(@UserEmail VARCHAR(100) = NULL,      
@FromDate VARCHAR(10) = NULL,      
@ToDate VARCHAR(10) = NULL)      
AS      
BEGIN      
--SELECT       
--GETDATE() AS [Date]      
--,CONVERT(VARCHAR(10),GETDATE(),103) AS ShortDate      
--,'Monday' as [DayName]      
--,'Full Day' as [Session]      
--, 1 as NoOfDays      
--,'Work Day' as [Status]      
--,'W' as StatusCode      

DECLARE @Result AS TABLE    
(    
Date       DATETIME,
ShortDate  VARCHAR(50),
DayNames   VARCHAR(50),    
Session    VARCHAR(20),    
NoOfDays   INT,
Status     VARCHAR(50),    
StatusCode VARCHAR(2)    
)        

DECLARE @dayscount INT 
SET @dayscount =  CAST(DATEDIFF(DAY, CONVERT(DATE, @FromDate, 103), CONVERT(DATE, @ToDate, 103)) AS INT) + 1
DECLARE @daysval INT
SET @daysval = 0
WHILE (@daysval < @dayscount)
BEGIN
INSERT INTO @Result (Date, ShortDate, DayNames, Session, NoOfDays, Status, StatusCode)    
VALUES (CONVERT(DATETIME, DATEADD(DAY, @daysval, CONVERT(DATE, @FromDate, 103)), 103), CONVERT(VARCHAR(50), DATEADD(DAY, @daysval, CONVERT(DATE, @FromDate, 103)), 103),     
DATENAME(dw, CONVERT(VARCHAR(50), DATEADD(DAY, @daysval, '07/02/2021'), 103)), 
'Full Session', 1, 'Work Day', 'W')    

SET @daysval = @daysval + 1
DECLARE @daycheck NVARCHAR(100)
SELECT @daycheck = DayNames 
FROM @Result

IF (@daycheck = 'sunday')
BEGIN
UPDATE @Result
SET Status = '', StatusCode = ''
END
END

SELECT * FROM @Result   
END

在执行我的查询之后，我得到这个结果:

EXEC [dbo].[usp_GetLeaveDateBewteenStartAndEndDate] NULL, '03/02/2021', '05/02/2021'
Date    ShortDate   DayNames    Session NoOfDays    Status  StatusCode  
-----------------------------------------------------------------------
2021-02-03 00:00:00.000 03/02/2021  Sunday  Full Session    1       
2021-02-04 00:00:00.000 04/02/2021  Sunday  Full Session    1       
2021-02-05 00:00:00.000 05/02/2021  Wednesday   Full Session    1   Work Day    W

对于01/02/2021,daynames应为星期一，05/02/2021应为星期二。

Answer 1

我不能肯定地说为什么你看到这些结果，因为我没有你的输入参数。但是有一些基本的错误

1. select @daycheck = DayNames from @Result不检查请求的是哪一行
2. update @Result set Status = '' , StatusCode = ''也有同样的问题。
3. 您正在将日期左右转换为varchar。保持值的格式，并在显示所需的地方转换。
4. 此外，您应该将From和To日期传递为date而不是varchar。
5. 不需要while循环或表变量来完成所有这些。可以用一个计数表来完成。我在下面使用了Itzik Ben-Gan的著名版本。

WITH
L0 AS ( SELECT 1 AS c 
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L3 ),
Dates AS (
SELECT TOP( DATEDIFF(day, @FromDate, @ToDate) + 1 )
DATEADD(day, rownum - 1, @FromDate) AS dateVal
FROM Nums
)
SELECT
dateVal AS Date,
CONVERT(Varchar(50), dateVal, 103) AS ShortDate,
v.DayNames,
'Full Session' AS Session,
1 AS NoOfDays,
CASE WHEN v.DayNames = 'Sunday' THEN '' ELSE 'Work Day' END AS Status,
CASE WHEN v.DayNames = 'Sunday' THEN '' ELSE 'W' END AS StatusCode
FROM Dates
CROSS APPLY (VALUES (DATENAME(dw, dateVal) ) ) AS v(DayNames)