Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deterministic scalar function to get day of week for a date

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
like image 866
David Avatar asked Nov 26 '09 13:11

David


1 Answers

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
like image 178
David Avatar answered Sep 29 '22 03:09

David