others - 在 SQL Server 中,如何显示天名称?

我在 SQL Server 上有一个查询

我想按如下所示显示:


CDATE | CDAY


2019-04-01 | Monday


2019-04-02 | Tuesday


... |.. ....


2019-04-30 | Tuesday



但我发现了如下错误:

转换日期和/或者字符字符串时转换失败。

如果有人可以帮助


DECLARE @V_DATE DATE = GETDATE()



;WITH CTE_DATE AS (


 SELECT DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE,


 DATENAME(dw, CONVERT(varchar, DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE))) CDAY


 UNION ALL


 SELECT DATEADD(dd,1,CDATE),


 DATENAME(dw, CONVERT(varchar, DATEADD(dw,1,CDAY)))


 FROM CTE_DATE


 WHERE DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))



 )


 SELECT * FROM CTE_DATE



时间:

你的问题是:


DATENAME(dw, DATEADD(dw, 1, CDAY))



我想你打算:


DATENAME(dw, DATEADD(dw, 1, CDATE))



我将把CTE写为:


WITH CTE_DATE AS (


 SELECT DATEADD(day, -(DAY(@V_DATE)-1),@V_DATE) as CDATE, 


 DATENAME(dw, DATEADD(day, -(DAY(@V_DATE) - 1), @V_DATE)) as CDAY


 UNION ALL


 SELECT DATEADD(day, 1, CDATE),


 DATENAME(dw, DATEADD(dw, 1, CDATE))


 FROM CTE_DATE


 WHERE DATEADD(day, 1, CDATE) <= DATEADD(day, -(DAY(DATEADD(month, 1, CDATE))), DATEADD(month, 1, CDATE))



 )


 SELECT *


 FROM CTE_DATE;



你不需要描述代码所要做的代码。 它对字符串有不必要的转换,对于你想做的事情可能会产生不必要的复杂。

不需要转换为varchar以获取工作日。


 UNION ALL


 SELECT DATEADD(dd,1,CDATE),


 DATENAME(dw, CONVERT(varchar, DATEADD(dw,1,CDAY))) -- No need to convert to varchar in order to get weekday.


 FROM CTE_DATE


 WHERE DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))



你可以使用datename函数直接获取它。


 DECLARE @V_DATE DATE = GETDATE()



 ;WITH CTE_DATE AS (


 SELECT DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE,


 DATENAME(dw, CONVERT(varchar, DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE))) CDAY


 UNION ALL


 SELECT DATEADD(dd,1,CDATE),


 DATENAME(dw, DATEADD(dd,1,CDATE)) -- modified 


 FROM CTE_DATE


 WHERE DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))



 )


 SELECT * FROM CTE_DATE



...