Setting 'DATEFIRST' in a User Defined Function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Setting 'DATEFIRST' in a User Defined Function

Author

Topic

hearnie
Starting Member

49 Posts

Posted - 2005-04-15 : 11:32:34

Does anyone know how to use the function call
SET DATEFIRST 1

in a user defined function.
It executes fine in a stored procedure but wont work within a function?

H.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-15 : 11:58:56

You can't use SET DATEFIRST 1 in a function.

Way are you trying to do?

CODO ERGO SUM

hearnie
Starting Member

49 Posts

Posted - 2005-04-15 : 12:08:01

Im trying to set the day to a Monday to do Weekday calculations.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-15 : 12:16:34

What do you mean by "weekday calulations". Perhaps you could explain exactly what you are trying to do.

There may be another way to do it without SET DATEFIRST that someone can help you with.

CODO ERGO SUM

hearnie
Starting Member

49 Posts

Posted - 2005-04-15 : 12:21:41

ok well I have this code which trys to calculate what Work Week it is in the calender as in Jan03 would be WW1 and so on and so on

-- Begin WW calculation
set @today= @date (taken in by the function)

set @i=0
WHILE @i <> @yeardays
BEGIN

set @ww=null
-- Sets Calendar year
select @yrs=datepart(yyyy,@today)

-- set @yr=@yrs
set DATEFIRST 1 -- Monday if first day week
if datepart(mm,@today)=12
set @jan12= datepart(dd,@today)-32
set @dow = DATEPART(dw,@today)

If @dow < 7
set @jan12=@jan12-@dow

If @jan12 > -7
set @yrs=@yrs+1
set @ww=1
break

if @ww is null
set @jan1=convert(int,CAST('1/1/' + convert(varchar,@yrs) AS smalldatetime))
set @dow= DATEPART(dw,@jan1) --monday if first day of week
If @dow < 7
set @jan1=@jan1-@dow
set @ww =(abs(datediff(dy,@today,@jan1))/7)+1
break

set @yr=@yrs

set @i=@i+1
set @today=@today+1

CONTINUE
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-15 : 13:46:02

You didn't actually post code that works, since it doesn't have its variable declarations, so it's a little hard to see what you are going for.

Are you trying to find the work week of year number? What is your definition of work week, and how do you decide when the year starts? Does is start on the first day of the year? How do you handle the end of the year; can the final week carry over into the next calendar year?

CODO ERGO SUM

hearnie
Starting Member

49 Posts

Posted - 2005-04-19 : 08:44:52

I know I didnt post code that works and didnt put in the decalarations. I only put a piece of it up to show you how Im using the DATEFIRST inside that procedure.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-19 : 09:14:53

OK, I guess I answered you original question about using SET DATAFIRST in a function.

Since you seem reluctant to give any explanation of what it is you are trying to get your function to do or to answer any of the questions I posted, I will assume that you don't need any help with it and let it go at that.

quote:
Originally posted by hearnie

I know I didnt post code that works and didnt put in the decalarations. I only put a piece of it up to show you how Im using the DATEFIRST inside that procedure.

CODO ERGO SUM

SamHills
Starting Member

1 Post

Posted - 2008-11-19 : 17:53:32

I needed to do something similar, so I wrote this function:

quote:

-- Function to calculate the Day Of Week when the week doesn't start on Sunday
-- (Needed because SET DATEFIRST can't be used in a function)

CREATE FUNCTION dbo.f_DOW( @Dt SMALLDATETIME, @DateFirst TINYINT )
RETURNS INT AS BEGIN
RETURN (DATEPART( dw, @Dt ) + 6 - @DateFirst) % 7 + 1
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 02:02:48

DATENAME function is not dependant on SET DATEFIRST setting.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 03:13:33

quote:
Originally posted by hearnie

Im trying to set the day to a Monday to do Weekday calculations.

assuming your datefirst setting is 7 which is default,cant you just check where datepart(dw,yourdate) not in (1,7) to exclude saturday,sunday?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-20 : 12:43:38

DATENAME is dependant in the language setting and DATEPART(DW,YOURDATE) is dependant in the setting of DATEFIRST.

The following code works with any setting of either to select only weekdays:

where
	datediff(dd,,-53690,getdate())%7 < 5

CODO ERGO SUM

v-jaschu
Starting Member

2 Posts

Posted - 2009-06-05 : 10:54:17

I have an actual example of using SET DATEFIRST in a function. I would like to use fnGetWeek() in the select.

select fnGetWeek('6/5/2009', 'FRIDAY') [Week]
this is the expected value 2009 6/5 - 6/11

I am getting this message when I try to create the function. Is there a better way to do this?

Invalid use of a side-effecting operator 'SET COMMAND' within a function.

CREATE FUNCTION fnGetWeek(@curDate datetime, @startDay varchar(10))
RETURNS nvarchar(70)
AS
BEGIN
/*
SELECT dbo.fnGetWeek('2004-09-21', 'MONDAY')
*/

--declare @curDate datetime
--declare @startDay varchar(10)

set @curDate = getdate()
set @startDay = 'FRIDAY'

IF @startDay = 'MONDAY'
SET DATEFIRST 1
ELSE IF @startDay = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @startDay = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @startDay = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @startDay = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @startDay = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @startDay = 'SUNDAY'
SET DATEFIRST 7

DECLARE @weekStartDate DATETIME, @weekEndDate DATETIME
--GET THE WEEK START DATE
SELECT @weekStartDate = @curDate - (DATEPART(DW, @curDate) - 1)

--GET THE WEEK END DATE
SELECT @weekEndDate = @curDate + (7 - DATEPART(DW, @curDate))

--PRINT 'Week Start: ' + CONVERT(VARCHAR, @weekStartDate)
--PRINT 'Week End: ' + CONVERT(VARCHAR, @weekEndDate)

declare @text nvarchar(70)

set @text = CAST(DATEPART(Year, @weekStartDate) as varchar) + ' '
set @text = @text + CAST(DATEPART(Month, @weekStartDate) as varchar) + '/'
set @text = @text + CAST(DATEPART(Day, @weekStartDate) as varchar) + ' - '
set @text = @text + CAST(DATEPART(Month, @weekEndDate) as varchar) + '/'
set @text = @text + CAST(DATEPART(Day, @weekEndDate) as varchar)

return @text

END

-Jim

v-jaschu
Starting Member

2 Posts

Posted - 2009-06-05 : 11:14:57

Just call SET DATEFIRST before the function is called.

-Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources