SQL-SERVER Quarter Function

Question

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 1

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

Answer 2

It seems so much simpler to do:

select 'Q' + datename(quarter, dateadd(month, -3, @date))

As for your question, the way to call a function is using SELECT:

SELECT @Qat = sf_Quarters(@DATE);

You are calling the function as if it were a stored procedure. I'm surprised that works at all.