Deterministic scalar function to get day of week for a date

sql-server, user-defined-functions

Solution

Ok, i figured it..

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

Problem

SQL Server, trying to get day of week via a deterministic UDF. Im sure this must be possible, but cant figure it out. UPDATE: SAMPLE CODE.. ``` 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 ```

Original source