Converting to decimal

[Shawn]

I've imported some data from a mainframe db via text
file. The file contains this:

credit_hours pic 9(2)v9 (COBOL, it basically says 3 number
fields with an implied decimal before the last number)

For simplicity, I imported it as char(3)

Now it shows up in SQL as:
050

Using the cast command, I would like it to show up as:
05.0 (my cast statement -> select cast(credit_hours as dec
(3,1)) from table) now it show as this -> 50.0

How can I get it in the desired output using the cast
command or any other suggestions?

Thanks

[Aaron Bertrand - MVP]

> For simplicity, I imported it as char(3)

>
> Now it shows up in SQL as:
> 050
>
> Using the cast command, I would like it to show up as:
> 05.0 (my cast statement -> select cast(credit_hours as dec
> (3,1)) from table) now it show as this -> 50.0
>
> How can I get it in the desired output using the cast
> command or any other suggestions?

Are there always three digits, and always a trailing number that is supposed
to represent the first decimal place? If so, then:

SELECT CONVERT
(
DECIMAL(3, 1),
LEFT(credit_hours, 2) + '.' + RIGHT(credit_hours, 1)
)
FROM table

You could also use SUBSTRING(), or STUFF().

However, instead of worrying about presentation, you should focus on getting
the data stored in the right way.

[Shawn]

Correct you are, if I have it stored correctly then I
would not need to worry about this. I wanted to test the
DTS import first to ensure the data was acceptable, now I
will change my DTS import to import the decimal fields the
correct way. When I tried to import is using dec(3,1) it
did not work correctly, but I think I will need to run a
line of T-SQL script to alter the data after it is in the
Db as part of the DTS. DOes that sound correct? Yes, the
field is always 3 digits trailing number that is supposed
to represent the first decimal place.

Thanks

>-----Original Message-----

>> For simplicity, I imported it as char(3)
>>
>> Now it shows up in SQL as:
>> 050
>>
>> Using the cast command, I would like it to show up as:
>> 05.0 (my cast statement -> select cast(credit_hours as

dec

>> (3,1)) from table) now it show as this -> 50.0
>>
>> How can I get it in the desired output using the cast
>> command or any other suggestions?

>
>Are there always three digits, and always a trailing

number that is supposed

>to represent the first decimal place? If so, then:
>
>SELECT CONVERT
>(
> DECIMAL(3, 1),
> LEFT(credit_hours, 2) + '.' + RIGHT(credit_hours, 1)
>)
>FROM table
>
>You could also use SUBSTRING(), or STUFF().
>
>However, instead of worrying about presentation, you

should focus on getting

>the data stored in the right way.
>
>
>.
>

[Peter Chong]

Hi, Shawn
dim dec 'as double
dim pic 'as string
dec = (val(pic)) / 10 <-- Implied decimal(V) with 1/10.

"Shawn" <programming@jards.com> wrote in message news:<005101c367f0$4c433230$a101280a@phx.gbl>...

> I've imported some data from a mainframe db via text
> file. The file contains this:
>
> credit_hours pic 9(2)v9 (COBOL, it basically says 3 number
> fields with an implied decimal before the last number)
>
> For simplicity, I imported it as char(3)
>
> Now it shows up in SQL as:
> 050
>
> Using the cast command, I would like it to show up as:
> 05.0 (my cast statement -> select cast(credit_hours as dec
> (3,1)) from table) now it show as this -> 50.0
>
> How can I get it in the desired output using the cast
> command or any other suggestions?
>
> Thanks