November 2, 2009 at 12:22 pm
I need to temporarily change the DateFirst setting within a function. When I try to add the statement
set datefirst 6
to my function,
I get the following error:
Msg 443, Level 16, State 15, Procedure fnMfgMonth, Line 26
Invalid use of side-effecting or time-dependent operator in 'SET COMMAND' within a function.
How do I work around this?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
November 2, 2009 at 12:29 pm
You can't change settings in a UDF. Will need to be a proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2009 at 12:33 pm
For my curiosity, why do you need to change the DATEFIRST within your function?
Probably there is another solution.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
November 2, 2009 at 1:33 pm
I'd be interested as well.
November 2, 2009 at 1:46 pm
Ok, I'll attempt to explain why. My function fnMfgMonth calculates the "manufacturing month" for a given date. "Manufacturing month" is determined by the week # of the year, and the manufacturing month always starts on a Saturday.
So, I need to determine the week #, assuming the week starts on Saturday rather than Sunday.
In MS Access, the DatePart function allows you to specify the first day of the week:
intWeek = DatePart("ww", #mydate#, 7)
But the SQL DatePart function does not have that option.
The manufacturing month of 11/09 started on 10/30/09. But right now my SQL function shows 10/30/09 as still in the 10/09 manufacturing month.
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
November 2, 2009 at 2:00 pm
This should be fairly easy to do in SQL, but you really haven't really explained the rules for when the manufacturing year and manufacturing month start, so we can't really give you a solution.
If you need the start of week for a week starting Saturday (or any other day of week) the function on the link below will give you that.
Start of Week Function
November 2, 2009 at 2:23 pm
I generally find that a calendar table is usually the solution for this (and many other) date related issues
November 2, 2009 at 2:51 pm
I'll second the motion for a calendar table. Makes all of these things much easier to solve.
Beyond that, why not just add 1 to the day before you run the datepart week function? Won't change the week for anything but a Saturday, but will change Saturdays.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2009 at 3:11 pm
I may yet go with the calendar table idea. However, the rules for determining manfacturing month seem to be fairly clear:
Manufacturing year is divided into four 13-week quarters.
Manufacturing months contain either 4 or 5 Saturday-to-Friday weeks.
The pattern for each quarter is:
month 1 has 4 weeks
month 2 has 4 weeks
month 3 has 5 weeks
The odd month is January:
The year always starts on January 1st. If January 1st falls on Mon, Tue, or Wed, then the week of January 1st is considered the first full week of the year. Otherwise, the manufacturing month of January will be from 1/1 to (first saturday in January + 4 weeks).
My current function (warts and all) is shown below.
It works fine except for the Saturday issue. For example, 10/31/09 shows as mfgmonth October, when it should be November.
create FUNCTION [dbo].[fnMfgMonth]
(
-- Add the parameters for the function here
@DCalendarDate datetime
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @MfgMonth datetime
declare @intWeek int
declare @strYear nvarchar(4)
declare @strmonth nvarchar(3)
declare @yearstart as datetime
-- Add the T-SQL statements to compute the return value here
--added 11/2/09
set @stryear=convert(varchar,datepart("yyyy",@dcalendardate))
set @yearstart=convert(datetime,'1/1/' + @stryear)
set @intweek=case when datepart(dw,@yearstart)>4
then datepart("ww",@dcalendardate)-1
else datepart("ww",@dcalendardate)
end
--end of 11/2/09 addition
set @strmonth=
case
when @intweek <= 4 then '1/'
when @intweek <= 8 then '2/'
when @intweek <=13 then '3/'
when @intweek <=17 then '4/'
when @intweek <=21 then '5/'
when @intweek <=26 then '6/'
when @intweek <=30 then '7/'
when @intweek <=34 then '8/'
when @intweek <=39 then '9/'
when @intweek <=43 then '10/'
when @intweek <=47 then '11/'
else '12/'
end
-- Return the result of the function
select @MfgMonth= @strmonth+'1/'+@stryear
return @MfgMonth
END
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
November 2, 2009 at 3:45 pm
Bite the bullet , its a small one, and go for the table.
The reason i like a table is so that not only can you ask the question , which manufacturing month is this date in, but also which dates are in this manufacturing month. If you were to ask that question using a function , heaven know the amount of additional resources that would be required.
The table population script can be as convoluted as you like , it can contain all your exceptions to rules, take a long time to run , but once it has then you have a very neat simple dataset to work against.
November 2, 2009 at 3:51 pm
Yeah, I think you're right....
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 3, 2010 at 6:08 am
all the information you need is available within the function
so adjust for the error introduced by @@datefirst
I think this would work:
create function dbo.fnWeekDay(@Now datetime, @DateFirst int)
returns int
begin
--
return ((datepart(weekday, @Now) + @@datefirst - @DateFirst + 6) % 7) + 1
--
end
July 3, 2010 at 11:31 am
She Through Whom All Data Flows (11/2/2009)
Ok, I'll attempt to explain why. My function fnMfgMonth calculates the "manufacturing month" for a given date. "Manufacturing month" is determined by the week # of the year, and the manufacturing month always starts on a Saturday.So, I need to determine the week #, assuming the week starts on Saturday rather than Sunday.
In MS Access, the DatePart function allows you to specify the first day of the week:
intWeek = DatePart("ww", #mydate#, 7)
But the SQL DatePart function does not have that option.
The manufacturing month of 11/09 started on 10/30/09. But right now my SQL function shows 10/30/09 as still in the 10/09 manufacturing month.
How do you define the first week of the year?
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply