Philip Mather Philip Mather - 2 months ago 24
SQL Question

SQL-SERVER Quarter Function

I've written an SQL function to turn an INT (month value) into a VARCHAR representing the Quarter of the year.

My code is as follows.

ALTER FUNCTION [sf_Quarters] (@DATE date)
Returns VARCHAR(4)
WITH EXECUTE AS CALLER
AS
BEGIN

DECLARE @QUARTER VARCHAR(4) = '';
DECLARE @DATEMONTH INT = DATEPART(MONTH, @DATE);

SELECT @QUARTER =
CASE
WHEN @DATEMONTH > 3 AND @DATEMONTH < 7 THEN 'Q1'
WHEN @DATEMONTH > 6 AND @DATEMONTH < 10 THEN 'Q2'
WHEN @DATEMONTH > 9 AND @DATEMONTH <= 12 THEN 'Q3'
WHEN @DATEMONTH > 0 AND @DATEMONTH < 4 THEN 'Q4'
ELSE NULL
END
RETURN @QUARTER
END


For some reason when I pass this function a valid date object (in my testcase) I only get the single char 'Q' back out.

DECLARE @DATE DATE = '20090405'
DECLARE @Qat VARCHAR

EXEC @Qat = sf_Quarters @DATE

SELECT @Qat


I realise I'm probably doing something daft thats breaking it. I just cant understand why it's only returning the first of the CHARS.

I have also substituted my fixed date string with the function (Current_Timestamp) and got the same result.

Answer

The problem is in the second part where you are calling the function -

DECLARE @DATE DATE = '20090405'
DECLARE @Qat VARCHAR  -- change it to VARCHAR(4)
--varchar [ ( n | max ) ]
--When n is not specified in a data definition or variable declaration statement, the default length is 1
EXEC @Qat = sf_Quarters @DATE

SELECT @Qat

Note: Follow the way Gordon suggested to invoke a function

Comments