Deterministic scalar function to get day of week for a date

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

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.

Answer 4

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

;
with 
Dates(DateValue) as 
(
    select cast('2000-01-01' as date)
    union all 
    select dateadd(day, 1, DateValue) from Dates where DateValue < '2050-01-01'
)
select 
    year(DateValue) * 10000 + month(DateValue) * 100 + day(DateValue) as DateKey, DateValue,        
    datediff(day, dateadd(week, datediff(week, 0, DateValue), 0), DateValue) + 2 as DayOfWeek,
    datediff(week, dateadd(month, datediff(month, 0, DateValue), 0), DateValue) + 1 as WeekOfMonth,
    datediff(week, dateadd(year, datediff(year, 0, DateValue), 0), DateValue) + 1 as WeekOfYear
    from Dates option (maxrecursion 0)

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

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 6

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 7

Make a function, and have @dbdate varchar(8) as your input variable.

Have it return the following:

RETURN (DATEDIFF(dd, -1, convert(datetime, @dbdate, 112)) % 7)+1;

The value 112 is the sql style YYYYMMDD.

This is deterministic because the datediff does not receive a string input, if it were to receive a string it would no longer work because it internally converts it to a datetime object. Which is not deterministic.

Answer 8

Not sure what you are looking for, but if this is part of a website, try this php function from http://php.net/manual/en/function.date.php

function weekday($fyear, $fmonth, $fday) //0 is monday
{
  return (((mktime ( 0, 0, 0, $fmonth, $fday, $fyear) - mktime ( 0, 0, 0, 7, 17,   2006))/(60*60*24))+700000) % 7;
}

Answer 9

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

DATEPART(weekday, YEAR_DATE)

Answer 10

Can't you just select it with something like:

SELECT DATENAME(dw, GETDATE());