convert decimal value of persons height into feet/inches

given HeightInches decimal (18, 0))

and

INSERT INTO PatientVisits (PatientID,HeightInches)

select '1234-12', '68.5' -- would like to convert 68.5 to 5' 8 1/2"

how would I extract the value in a select statement of '68.5' to display in feet and inches rather than a decimal value?

Thank you!

Greg

If you put a question about using feet or inch data type, there is no answer.

But you can create a new data type with CLR User-Defined Types.

According this you can transform from inch to feet like this

select cast('68.5' as decimal)*0.08333

|||

Thank you Gigi.

I edited my question to be more clear I hope.

Greg

|||Maybe you can get the source code of converting inches to feet and inches from here.
Then adapt that code in a T-SQL function that have parameter 68.5(decimal) and return 5' 8 1/2 (a string)|||

Use the following code,

Code Snippet

Create function InchesToFeet(@.in float)

returns varchar(20)

as

Begin

declare @.feet varchar(20)

set @.feet= ''

select

@.feet = cast(floor(V) as varchar) + ' ft' +

case

when (@.in - floor(floor(V) * 1/(2.54 / 30.48))) = 0 then

''

else

' and '

+ cast((@.in - floor(floor(V) * 1/(2.54 / 30.48))) as varchar)

+ ' Inches' end

from

(

select

cast(@.in * (2.54 / 30.48) as varchar) as V

) as d;

return @.feet;

End

Go

select dbo.InchesToFeet(68.5)

|||

I would suggest that you use the front end code to do this, rather than SQL code. I am sure the function posted will work (well, I didn't test it, but I have faith it will probably work) but you can see the code is painful at best. In the client you should be able to use far eaiser to manage code, in the UI, which is made to display data for the user.