June 17, 2010 at 12:50 pm
I'm trying to create a stored procedure that will take a datetime variable, strip out the month, and return 1 of 4 ints (basically, the month that defines the start of each quarter for the year), below is the code I'm using, but it's throwing an error the line beneath "declare @month int" saying the syntax near '@month' in '@month= ...' is incorrect.
Any ideas? it seems fairly straightforward to me, so I'm not sure what's wrong.
-----------------------------The Code-----------------------------
create PROCEDURE [dbo].[getqtr] (@today datetime)
AS
BEGIN
declare @month int
@month = datepart(month,@today)
case @month
when @month is between 1 and 3 then @month = 1
when @month is between 4 and 6 then @month = 4
when @month is between 7 and 9 then @month = 7
when @month is between 10 and 12 then @month = 10
END
return @month
GO
June 17, 2010 at 1:20 pm
There a several ways to get the quarter of a given date (in the order of my personal preference for result in an INT format):
1) use a calendar table that would hold the data, week, month, quarter, year, fiscal_year, IsWorkingDay a.s.o. and a join to this calendar table.
2) SELECT ((MONTH(@ThisDate)-1)/3*3)+1
3) SELECT MONTH(DATEADD(qq, DATEDIFF(qq, 0, @ThisDate), 0))
Option 2 and 3 could be wrapped into an inlineTableValuedFunction (iTVF) if needed.
Regarding the approach you're using: There are several syntax errors in your sproc. Corrected version:
DECLARE @month INT
SELECT @month = DATEPART(MONTH,@ThisDate)
SELECT @month =
CASE
WHEN @month BETWEEN 1 AND 3 THEN 1
WHEN @month BETWEEN 4 AND 6 THEN 4
WHEN @month BETWEEN 7 AND 9 THEN 7
WHEN @month BETWEEN 10 AND 12 THEN 10
END
SELECT @month
Side note: I didn't use code tags on purpose since I don't want to draw attention to this piece of code... 😉
June 17, 2010 at 1:30 pm
Ack! Completely forgot about the 'qq' param for datediff! No need for a separate procedure now. I'll stick with door number 2 as a better solution. Thanks for your help!
June 17, 2010 at 1:53 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply