Deterministic scalar function to get day of week for a date

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

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)

I know this post is way-super-old, but I was trying to do a similar thing and came up with a different solution and figured I'd post for posterity. Plus I did some searching around and did not find much content on this question.

In my case, I was trying to use a computed column PERSISTED, which requires the calculation to be deterministic. The calculation I used is:

datediff(dd,'2010-01-03',[DateColumn]) % 7 + 1

The idea is to figure out a known Sunday that you know will occur before any possible date in your table (in this case, Jan 3 2010), then calculate the modulo 7 + 1 of the number of days since that Sunday.

The problem is that including a literal date in the function call is enough to mark it as non-deterministic. You can work around that by using the integer 0 to represent the epoch, which for SQL Server is Jan 1st, 1900, a Sunday.

datediff(dd,0,[DateColumn]) % 7 + 1

The +1 just makes the result work the same as datepart(dw,[datecolumn]) when datefirst is set to 7 (default for US), which sets Sunday to 1, Monday to 2, etc

I can also use this in conjunction with case [thatComputedColumn] when 1 then 'Sunday' when 2 then 'Monday' ... etc. Wordier, but deterministic, which was a requirement in my environs.