February 23, 2013 at 7:31 pm
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.
February 23, 2013 at 7:52 pm
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?
February 23, 2013 at 8:31 pm
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.
February 23, 2013 at 8:54 pm
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)))
February 24, 2013 at 4:00 am
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.
February 24, 2013 at 5:29 am
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
February 24, 2013 at 5:49 am
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
February 24, 2013 at 3:48 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply