Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Determenistic function returns undetermenistic. Why? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, February 23, 2013 7:31 PM
 Grasshopper Group: General Forum Members Last Login: Sunday, July 20, 2014 4:47 PM 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.
Post #1423382
 Posted Saturday, February 23, 2013 7:52 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:38 AM Points: 23,516, Visits: 37,738
Post #1423386
 Posted Saturday, February 23, 2013 8:31 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, October 24, 2015 2:31 AM Points: 3,158, Visits: 11,771
 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.
Post #1423388
 Posted Saturday, February 23, 2013 8:54 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:38 AM Points: 23,516, Visits: 37,738
 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)))`
Post #1423390
 Posted Sunday, February 24, 2013 4:00 AM
 Grasshopper Group: General Forum Members Last Login: Sunday, July 20, 2014 4:47 PM 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.
Post #1423404
 Posted Sunday, February 24, 2013 5:29 AM
 Grasshopper Group: General Forum Members Last Login: Sunday, July 20, 2014 4:47 PM 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
Post #1423407
 Posted Sunday, February 24, 2013 5:49 AM
 Grasshopper Group: General Forum Members Last Login: Sunday, July 20, 2014 4:47 PM 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`
Post #1423409
 Posted Sunday, February 24, 2013 3:47 PM
 Grasshopper Group: General Forum Members Last Login: Sunday, July 20, 2014 4:47 PM 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`
Post #1423448

 Permissions