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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With