I am still learning SQL so this may seem a very odd question, but is this the best way to use CASE within a CASE to check for NULL?
@FN_InputDt datetime)
RETURNS varchar(3)
as
BEGIN
DECLARE @Result varchar(3),
@MonthNo int
Set @MonthNo = datepart(m,@FN_InputDt)
Set @Result =
CASE WHEN @FN_InputDt IS NOT NULL then
CASE @MonthNo
WHEN 1 then 'JAN'
WHEN 2 then 'FEB'
WHEN 3 then 'MAR'
WHEN 4 then 'APR'
WHEN 5 then 'MAY'
WHEN 6 then 'JUN'
WHEN 7 then 'JUL'
WHEN 8 then 'AUG'
WHEN 9 then 'SEP'
WHEN 10 then 'OCT'
WHEN 11 then 'NOV'
WHEN 12 then 'DEC'
END
END
RETURN @Result
END
From stackoverflow
-
If @FN_InputDt is null then @MonthNo will also be null, so you can just skip checking if @FN_InputDt is null.
You can skip the other case also by just using @MonthNo as index to pick part of a string:
set @Result = substring( 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC', @MonthNo * 3 - 2, 3 )If @MonthNo is null @Result will also be null.
JMS49 : Awesome! Thanks so much.Hogan : -1, See Jeff's comment, there is a library function to get the month nameGuffa : @Hogan: Yes, that alternative is well worth mentioning, but it doesn't give the exact same result as the function in the question.Hogan : @Guffa : Good to know, how is it different? -
Set @Result = Left(DateName(m,@FN_InputDt) , 3)
This converts the month to the name and only displays the first 3 characters.
Hogan : @jeff : isn't that DatePart not DateName?Jeff O : @Hogan - DatePart would return the numerical value of the month and not the actual name of the month. 12 instead of 'Dec' for example.
0 comments:
Post a Comment