views:

184

answers:

5

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

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;
}
Martin Andersson
Thanks but im looking for an SQL Server scalar function
Dve
A: 

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

Alex Bagnolini
DatePart is non-deterministic, so this is not possible
Dve
A: 

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

DATEPART(weekday, YEAR_DATE)
Maximilian Mayerl
DatePart is non-deterministic, so this is not possible
Dve
A: 

Can't you just select it with something like:

SELECT DATENAME(dw, GETDATE());
dnagirl
+2  A: 

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
Dve
I just posted you should make your implementation :) +1 for you
Alex Bagnolini