CODEWITHSUNDEEP
sqlsql-servert-sql
lifeisajourney
lifeisajourney

Reputation: 127

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

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

Upvotes: 0

Views: 95

Answers (3)

sa-es-ir
sa-es-ir

Reputation: 5042

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

Upvotes: 1

Paul Smith
Paul Smith

Reputation: 48

The following snippet shows both the calculation and what it produces for values of month between 1 and 36

declare @month INT = 0

while @month < 36 begin

    set @month = @month + 1

    -- This is the actual "function"

    select case when @month < 12 then concat (@month, ' month(s)')
                else concat (cast (@month/12.0 as decimal (6,2)), ' year(s)')
                end

end

Upvotes: 1

Jeff Moden
Jeff Moden

Reputation: 3494

Please try this...

 SELECT IIF(@month<12,CONCAT(@Month,' Months'),CONCAT(CONVERT(DECIMAL(9,1),@Month/12.0),' Year'));

Upvotes: 1

Related Questions