I want to write a function that takes the number of months as a parameter and does the following: I can hardcode all of this using case statements but I wanted to know if there is a dynamic way of doing it. Thanks! Answer Try this:

Calculate years based on months in SQL Server

I want to write a function that takes the number of months as a parameter and does the following:

IF @months = 3 THEN '3 Months'
IF @months = 6 THEN '6 Months'
IF @months = 12 THEN '1 Year'
IF @months = 18 THEN '1.5 Year'
IF @months = 24 THEN '2 Year'

.... and so on

​x
 
IF @months = 3 THEN '3 Months'IF @months = 6 THEN '6 Months'IF @months = 12 THEN '1 Year'IF @months = 18 THEN '1.5 Year'IF @months = 24 THEN '2 Year'​.... and so on​

I can hardcode all of this using case statements but I wanted to know if there is a dynamic way of doing it. Thanks!

Answer

Try this:

DECLARE @month INT=26
SELECT CASE WHEN @month >=12 
THEN CONCAT(CAST(@month/12.0 AS DECIMAL(5,1)),' Year') 
ELSE CONCAT(@month,' Months') END

 
DECLARE @month INT=26SELECT CASE WHEN @month >=12 THEN CONCAT(CAST(@month/12.0 AS DECIMAL(5,1)),' Year') ELSE CONCAT(@month,' Months') END ​

Advertisement

Answer