Showing posts with label persons. Show all posts
Showing posts with label persons. Show all posts

Sunday, February 12, 2012

Convert DT_14 to DT_18


Hi!
I have a Fact_data flow with several Lookups.
In one there's OLE Db source DT_14 (persons id's) which need to be compared to a column DT_18 from Dim_salesperson. Thats how I could collect the ID's from Dim_salesperson into OLE DB Destination. How could I compare, DT_14 data with DT_18?
First converting DT_14 to character. Secondly char to DT_18?
I would be so grateful if someone could give an idea...

curiousss wrote:



First converting DT_14 to character. Secondly char to DT_18?

Assuming you are talking about DT_I4 and DT_I8; that approach sounds reasonable. Have you try it?

|||Thanks, problem solved!

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.