Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

convert a temperature in Kelvin to Celcius and Fahrenheit.

Status
Not open for further replies.

pinkexpert

Programmer
Feb 7, 2011
11
US
can someone plase provide the formula to convert a temperature in Kelvin to Celcius and Fahrenheit.

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


-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
 
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
 
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
 
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top