## Determenistic function returns undetermenistic. Why?

 Author Message Champagne Charly Grasshopper Group: General Forum Members Points: 11 Visits: 122 Hello,Has any idea why this is classed as non deterministic. DATEFROMPARTS(DatePart(yyyy,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(mm,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(dd,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)))DATEFROMPARTS is supposedly deterministic in 2012 however the above throws a deterministic error.Hope somebody can spot how to improve so it is. Lynn Pettis SSC-Insane Group: General Forum Members Points: 24189 Visits: 37956 Champagne Charly (2/23/2013)Hello,Has any idea why this is classed as non deterministic. DATEFROMPARTS(DatePart(yyyy,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(mm,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(dd,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)))DATEFROMPARTS is supposedly deterministic in 2012 however the above throws a deterministic error.Hope somebody can spot how to improve so it is.You know, it could just be that I am physically tired after work four youth soccer games today, but I look at what is posted and have to ask, what are you trying to accomplish? What is with all the DATEADD and DATEPARTs doing to the value in Date_received? What is the purpose of this piece of code? Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Michael Valentine Jones Hall of Fame Group: General Forum Members Points: 3254 Visits: 11771 Champagne Charly (2/23/2013)Hello,Has any idea why this is classed as non deterministic. DATEFROMPARTS(DatePart(yyyy,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(mm,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)),DatePart(dd,DATEADD(dd, (DATEPART(dw, Date_received) - 1) * - 1, Date_received)))DATEFROMPARTS is supposedly deterministic in 2012 however the above throws a deterministic error.Hope somebody can spot how to improve so it is.You didn't post the code for the function.If you want some help with this, post the code within code blocks so we can actually read it. Lynn Pettis SSC-Insane Group: General Forum Members Points: 24189 Visits: 37956 I can do everything your code is doing without using DATEFROMPRARTS:`declare @Date_received date = '2013-02-23';select @Date_received, dateadd(dd, -1, dateadd(wk, datediff(wk, cast('1900-01-01' as date), @Date_received), cast('1900-01-01' as date)))select @Date_received, DATEFROMPARTS(DatePart(yyyy,DATEADD(dd, (DATEPART(dw, @Date_received) - 1) * - 1, @Date_received)), DatePart(mm,DATEADD(dd, (DATEPART(dw, @Date_received) - 1) * - 1, @Date_received)), DatePart(dd,DATEADD(dd, (DATEPART(dw, @Date_received) - 1) * - 1, @Date_received)))` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Champagne Charly Grasshopper Group: General Forum Members Points: 11 Visits: 122 Hi Michael,Currently needs to be in a persisted computed column hence no variables. It's not in a function. Once it works will no doubt put in function also. Champagne Charly Grasshopper Group: General Forum Members Points: 11 Visits: 122 Thanks Lynn,Date part was used as it is documented as determenistic. previously I had used: DATEADD(dd, (DATEPART(dw, Date_Received) - 1) * - 1, Date_Received)Unfortunately the formulae provided are not determenistic either and the first is only correct when @@DATEFIRST = 7 Although this is not ISO.I think the problem there in lies and that using DW is what is causing the issue. Am going to do a manual calc of weeks. between 19000101 and @date to work out which work day it is when startdate = monday. This hopefully will be deemed determenistic.Regards Champagne Charly Grasshopper Group: General Forum Members Points: 11 Visits: 122 Seems your use of datediff and 19000101 was one of the keys to help me solve the problem. Thank you. Following IS deterministic:`ALTER FUNCTION [dbo].[FctDateTime_FirstDayOfWeek]( @Date date)RETURNS datewith schemabindingASBEGINdeclare @firstDate datedeclare @Daysdiff int = dateDiff(dd,cast('1900-01-01' as date),@Date)%7select @firstDate = DATEADD(dd, @Daysdiff*-1, @Date) RETURN @firstDate END` Champagne Charly Grasshopper Group: General Forum Members Points: 11 Visits: 122 Not wanting to lleave the topic closed with incorrect info.. here are the Corrections.. :`Alter FUNCTION [dbo].[FctDateTime_FirstDayOfWeek2]( -- Add the parameters for the function here @Date date)RETURNS datewith schemabindingASBEGINdeclare @firstDate datedeclare @Daysdiff int = dateDiff(dd,(datefromparts('1900','01','01')),@Date)%7 --- ***Correct line. Cast is not determenisticselect @firstDate = DATEADD(dd, @Daysdiff*-1, @Date) RETURN @firstDate END`OR`Alter FUNCTION [dbo].[FctDateTime_FirstDayOfWeek2]( -- Add the parameters for the function here @Date date)RETURNS datewith schemabindingASBEGINdeclare @firstDate datedeclare @Daysdiff int = dateDiff(dd,(CONVERT(datetime, '1900-01-01', 101)),@Date)%7 ---*** Changed line. Convert is determenistic. select @firstDate = DATEADD(dd, @Daysdiff*-1, @Date) RETURN @firstDate END`