Geek Answers Handbook

Deterministic scalar function to get the day of the week for a date

SQL Server trying to get the day of the week through deterministic UDF.

I am sure that this should be possible, but I can not understand it.

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
+5
source share
10 answers

Ok, I figured it out.

 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
+6
source

A very similar approach to the above solution, but only a single-line one, which can be used inside a function or inline for a computed column.

Assumptions:

  • You have no dates until 1899-12-31 (this is Sunday).
  • Do you want to imitate @@ datefirst = 7
  • @dt is smalldatetime, datetime, date or datetime2

If you prefer it to be different, change the date "18991231" to a date with a weekday that you would like to be 1. The convert () function is key to making this all work - cast is NOT do the trick:

((dateiff (day, convert (date-time, '18991231', 112), @dt)% 7) + 1)

+6
source

Adapted from Deterministic scalar function to get week of year for 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)
+1
source

I know this post is super-old, but I tried to do a similar thing and came up with a different solution, and decided that I would publish for posterity. Plus, I searched a bit and did not find much content on this.

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

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

The idea is to find out the famous Sunday, which, as you know, will happen before any possible date in your table (in this case, January 3, 2010), then calculate modulo 7 + 1 the number of days from this Sunday.

The problem is that including a literal date in a function call is enough to mark it as non-deterministic. You can get around this by using the integer 0 to represent an era that for SQL Server is January 1, 1900, Sunday.

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

+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 surroundings.

+1
source

There is a built-in function in sql:

 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, since DATEPART(weekday) not deterministic.

UPDATE : DATEPART(weekday) not deterministic because it relies on DATEFIRST ( source ).
You can change it with SET DATEFIRST , but you cannot 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 looking at it at all, using for example Monday the first day).

0
source

The proposed solution has one problem - it returns 0 on Saturdays. Assuming we're looking for something compatible with DATEPART(WEEKDAY) , this is a problem.

CASE doesn’t fix anything simple.

0
source

Create a function and specify @dbdate varchar (8) as the input variable.

Let him return the following:

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

The value 112 is the sql style of YYYYMMDD.

This is deterministic because a dated character does not receive string input; if it receives a string, it will no longer work because it internally converts it to a datetime object. What is not deterministic.

0
source

Not sure what you are looking for, but if this is part of the 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; }
-one
source

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

 DATEPART(weekday, YEAR_DATE)
-one
source

Can't you just select it with something like:

 SELECT DATENAME(dw, GETDATE());
-one
source

More articles:


All Articles