January 21, 2008 at 12:38 pm
Hi I found a code sample at this site: --http://www.devx.com/tips/Tip/22210
that gives code to take a day and find the beginning date and end date of that week. I am getting an error message and not sure why as this is the first time that I have tried to create my own function.
I am trying to modify it to build a function in my db, here is what I have so far:
create function weekstart
(@REPORT_DATE DATETIME)
returns datetime
begin
declare @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2008-01-22T00:00:00'
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME
--GET THE WEEK START DATE
(SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1))
return @WEEK_START_DATE
end
I am getting the following error messages pointing to the @week_start_date parameter
Msg 156, Level 15, State 1, Procedure weekstart, Line 28
Incorrect syntax near the keyword 'SELECT'.
Msg 137, Level 15, State 1, Procedure weekstart, Line 28
Must declare the scalar variable "@WEEK_START_DATE".
Msg 137, Level 15, State 2, Procedure weekstart, Line 29
Must declare the scalar variable "@WEEK_START_DATE".
Thank you in advance
January 21, 2008 at 4:15 pm
Try using these functions. They're already debugged. 😎
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
End of Week Function:
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply