convert a temperature in Kelvin to Celcius and Fahrenheit.

[pinkexpert]

can someone plase provide the formula to convert a temperature in Kelvin to Celcius and Fahrenheit.

Thanks

 

[gmmastros]

We get this question a lot in the SQL Server forum. So often, in fact, that we created a simple link for the answer.

http://bit.ly/yHDkQe

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pinkexpert]

Thanks for the link.I have a formula in one of my stored procedure

/*Kelvin to Celcius conversion (C = K-273)*/

(CASE WHEN max(temp)IS NOT NULL THEN CAST(MAX(CAST(temp AS float) - 273) AS float) END) AS TempC.

/*Kelvin to Fahrenheit conversion (F = (9/5) C + 32)*/

(CASE WHEN max(temp) IS NOT NULL THEN CAST((((9 / 5) * (CAST(MAX(CAST(temp AS float) - 273)AS float))) + 32) AS float) END) AS TempF,

Some of the values looked strange (like -270 C) for instance.
So i am not sure if my formula is correct

 

[gmmastros]

In your sample code, you use temp (as a column name?). What is the data type for the temp column?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pinkexpert]

float

 

[gmmastros]

if the column is already float, then you are unnecesarily casting it, which is why I asked.

Anyway... I bet the problem is here.

[!](9 / 5)[/!]

SQL Server will see the 9 and the 5 as integers, and perform integer math. Ex:

Select 9 / 5 -- Result = 1

You should change that part to:

[!](9.0 / 5)[/!]

Now, SQL Server will see the 9.0 and assume a decimal. A decimal divided by an integer results in a decimal. Later, when you multiply this to your temp column, you will end up with a float.

By the way... all of the values in the temp column.... do they represent the temperature in Kelvin or Celsius?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pinkexpert]

The user has an option to enter the temperature in any format.If the user eneters the temp in centigrade or Farenheit there will be no conversion however if the user enters in kelvin then we convert it to degreeF and degreeC

 

[gmmastros]

So.... the temp column could represent temperature in Celsius or Fahrenheit, but not Kelvin? How will you know what is stored?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pinkexpert]

We are using the pivot functionality in our query and that is why we doing the casting otherwise i get the following error:

Disallowed implicit conversion from data type sql_variant to data type numeric, table 'pvt1', column '128'. Use the CONVERT function to run this query.

i followed your suggestion to change but i still get the temperature as -273.15 centigrade