Deterministic Scalar Function To Get Day Of Week For A Date

March 20, 2024 Post a Comment

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 A

Solution 1:

Ok, i figured it..

CREATEFUNCTION [dbo].[FN_DayNumeric_DateTime] 
(@DT DateTime)
RETURNSINTWITH SCHEMABINDING
ASBEGINDECLARE@ResultintDECLARE@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

Solution 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:

You don't have dates before 1899-12-31 (which is a Sunday)
You want to imitate @@datefirst = 7
@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)

Solution 3:

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.

Solution 4:

Taken from Deterministic scalar function to get week of year for a date

;
with 
Dates(DateValue) as 
(
    selectcast('2000-01-01'asdate)
    unionallselect dateadd(day, 1, DateValue) from Dates where DateValue <'2050-01-01'
)
selectyear(DateValue) *10000+month(DateValue) *100+day(DateValue) as DateKey, DateValue,        
    datediff(day, dateadd(week, datediff(week, 0, DateValue), 0), DateValue) +2as DayOfWeek,
    datediff(week, dateadd(month, datediff(month, 0, DateValue), 0), DateValue) +1as WeekOfMonth,
    datediff(week, dateadd(year, datediff(year, 0, DateValue), 0), DateValue) +1as WeekOfYear
    from Dates option (maxrecursion 0)

Solution 5:

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:

CREATEFUNCTIONDayOfWeek(@myDate DATETIME ) 
RETURNSintASBEGINRETURNDATEPART(weekday, @myDate)
ENDGOSELECTdbo.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).

Lab Sql