Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determenistic function returns undetermenistic. Why? Expand / Collapse
Author
Message
Posted Saturday, February 23, 2013 7:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 PM
Points: 11, Visits: 120
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 22,509, Visits: 30,229
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 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)
Post #1423386
Posted Saturday, February 23, 2013 8:31 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 22,509, Visits: 30,229
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 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)
Post #1423390
Posted Sunday, February 24, 2013 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 PM
Points: 11, Visits: 120
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 PM
Points: 11, Visits: 120
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 PM
Points: 11, Visits: 120
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

Post #1423409
Posted Sunday, February 24, 2013 3:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:11 PM
Points: 11, Visits: 120
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

Post #1423448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse