Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determenistic function returns undetermenistic. Why?


Determenistic function returns undetermenistic. Why?

Author
Message
Champagne Charly
Champagne Charly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

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





Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Champagne Charly
Champagne Charly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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
Champagne Charly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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
Champagne Charly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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 date
with schemabinding
AS
BEGIN
declare @firstDate date

declare @Daysdiff int = dateDiff(dd,cast('1900-01-01' as date),@Date)%7
select @firstDate = DATEADD(dd, @Daysdiff*-1, @Date)

RETURN @firstDate

END


Champagne Charly
Champagne Charly
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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 date
with schemabinding
AS
BEGIN
declare @firstDate date

declare @Daysdiff int = dateDiff(dd,(datefromparts('1900','01','01')),@Date)%7 --- ***Correct line. Cast is not determenistic

select @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 date
with schemabinding
AS
BEGIN
declare @firstDate date

declare @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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search