Deterministic scalar function to get day of week for a date

SQL Server, trying to get day of week via a deterministic UDF.

Im sure this must be possible, but can开发者_如何学Ct 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

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.

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)

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).

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.

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.

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;
}

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

DATEPART(weekday, YEAR_DATE)

Can't you just select it with something like:

SELECT DATENAME(dw, GETDATE());