Home > T-SQL > SSIS – Temperature conversion SQL Function – Script Download

In this article, I am sharing my code (SQL Function) that converts temperature measure form one to another. My SQL Function take temerature value and its measure charachter (C or F) and returns the other measure.

SQL Code

— =============================================
— Author:  Ayyappan Thangaraj, CEO: SQLServerRider.com
— Create date: 12/Jan/2012
— Description: Converts temperature Fahrenheit (°F) / Celsius (°C)
— =============================================
ALTER  FUNCTION Convert_Temperature
(
@temperature DECIMAL,
@Degree CHAR –F/C
)
RETURNS DECIMAL

AS
BEGIN
–This function converts temperature vaue from one measure to other.
–If you pass Celsius then it will return Fahrenheit and vice versa.
DECLARE @Result AS DECIMAL

IF UPPER(@Degree) = ‘F’
SET @Result = (@temperature – 32)* 5/  9 –F to C conversion
IF UPPER(@Degree) = ‘C’
SET @Result = ((@temperature * 9) /5 )+ 32 –C to F Conversion

RETURN  @Result
END

Execution

SELECT dbo.convert_temperature (70, ‘F’) AS ‘°C’
SELECT dbo.convert_temperature (21, ‘C’) AS ‘°F’

Result
°C
21

°F
70

Categories: T-SQL
1. July 31, 2012 at 2:23 pm

Thank you for sharing your code… It is very helpful. I was wondering if you could help me. my table does not tell me if the TEMP is C or F. Is there a way to create a function that tell me if TEMP < 50 then convert to F. ? any help would be appreciated.

Thank you again.

• July 31, 2012 at 3:08 pm

I assume you have a table with the following structure

Table Name: Weather
ID as INT
TEMP as SMALLINT

This select query givee you the result as your expectation

select ID,
case TEMP
when < 50 then Convert_Temperature(TEMP, 'C')
else TEMP
end as 'TEMP'
from Weather

2. August 1, 2012 at 9:21 am

Thank you.. I didn’t even think of doing it this way… i was going the route of changing the function.

3. August 1, 2012 at 2:26 pm