Deterministic scalar function to get day of week f

Question

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

Answer 1

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

Answer 2

Slightly similar approach to aforementioned solution, but just a one-liner that could be used inside a function or inline for computed column.

Assumptions:

1. You don't have dates before 1899-12-31 (which is a Sunday)
2. You want to imitate @@datefirst = 7
3. @dt is smalldatetime, datetime, date, or datetime2 data type

If you'd rather it be different, change the date '18991231' to a date with the weekday that you'd like to equal 1. The convert() function is key to making the whole thing work - cast does NOT do the trick:

((datediff(day, convert(datetime, '18991231', 112), @dt) % 7) + 1)

Answer 3

There is an already built-in function in sql to do it:

SELECT DATEPART(weekday, '2009-11-11')

EDIT: If you really need deterministic UDF:

CREATE FUNCTION DayOfWeek(@myDate DATETIME ) 
RETURNS int
AS
BEGIN
RETURN DATEPART(weekday, @myDate)
END
GO
SELECT dbo.DayOfWeek('2009-11-11')

EDIT again: this is actually wrong, as DATEPART(weekday) is not deterministic.

UPDATE: DATEPART(weekday) is non-deterministic because it relies on DATEFIRST (source).
You can change it with SET DATEFIRST but you can't call it inside a stored function.

I think the next step is to make your own implementation, using your preferred DATEFIRST inside it (and not considering it at all, using for example Monday as first day).

Answer 4

Can't you just select it with something like:

SELECT DATENAME(dw, GETDATE());

Answer 5

The proposed solution has one problem - it returns 0 for Saturdays. Assuming that we're looking for something compatible with DATEPART(WEEKDAY) this is an issue.

Nothing a simple CASE statement won't fix, though.

Answer 6

The day of the week? Why don't you just use DATEPART?

DATEPART(weekday, YEAR_DATE)