确定性标量函数来获取日期的星期几 [英] Deterministic scalar function to get day of week for a date

问题描述

SQL Server，尝试通过确定性 UDF 获取星期几.

SQL Server, trying to get day of week via a deterministic UDF.

我确定这一定是可能的，但无法弄清楚.

Im sure this must be possible, but cant figure it out.

更新:示例代码..

CREATE VIEW V_Stuff WITH SCHEMABINDING AS 
SELECT    
MD.ID, 
MD.[DateTime]
...
        dbo.FN_DayNumeric_DateTime(MD.DateTime) AS [Day], 
        dbo.FN_TimeNumeric_DateTime(MD.DateTime) AS [Time], 
...
FROM       {SOMEWHERE}
GO
CREATE UNIQUE CLUSTERED INDEX V_Stuff_Index ON V_Stuff (ID, [DateTime])
GO

推荐答案

好吧，我想到了..

CREATE FUNCTION [dbo].[FN_DayNumeric_DateTime] 
(@DT DateTime)
RETURNS INT WITH SCHEMABINDING
AS 
BEGIN
DECLARE @Result int 
DECLARE  @FIRST_DATE        DATETIME
SELECT @FIRST_DATE = convert(DATETIME,-53690+((7+5)%7),112)
SET  @Result = datediff(dd,dateadd(dd,(datediff(dd,@FIRST_DATE,@DT)/7)*7,@FIRST_DATE), @DT)
RETURN (@Result)
END
GO

这篇关于确定性标量函数来获取日期的星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！