ckangas7
asked on
Converting varchar hours and minutes to 2 place decimal
I have raw data being imported into a table as varchar and need to convert it into numeric 2 place decimal format. The data comming represents time in terms of hour and minutes. Data looks like this:
0000000
0015000
0030000
0730000
When converted it should look like:
0.00
0.25
0.50
7.50
I tried:
Insert into t_employee_detail
(emp_time)
select CAST(f7 as numeric(8, 2))
from t_insert_emp_detail
But that didn't do it. How do I do this?
0000000
0015000
0030000
0730000
When converted it should look like:
0.00
0.25
0.50
7.50
I tried:
Insert into t_employee_detail
(emp_time)
select CAST(f7 as numeric(8, 2))
from t_insert_emp_detail
But that didn't do it. How do I do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry about the proposed solution, I normally only post as comments must have clicked on Proposed by accident.
Cheers, Andrew
Cheers, Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or for an all numeric calculation:
Insert t_employee_detail(emp_time
Select CAST((CAST(f7 as int) / 100000) +
((CAST(f7 as int) / 1000) % 100) / 100.0 as numeric(8,2))
From t_insert_emp_detail
Insert t_employee_detail(emp_time
Select CAST((CAST(f7 as int) / 100000) +
((CAST(f7 as int) / 1000) % 100) / 100.0 as numeric(8,2))
From t_insert_emp_detail
ASKER
Hi,
Great input guys. One thing. After I insert the data it is automatically being rounded up to the nearest whole number instead of displaying in the 2 place decimal format that I need. The column type shows as [emp_time] [decimal](10, 0) NULL . I tried to change it to change it with:
ALTER table t_employee_detail
modify emp_time decimal (8,2)
but am getting a syntax error. What am I doing wrong?
Great input guys. One thing. After I insert the data it is automatically being rounded up to the nearest whole number instead of displaying in the 2 place decimal format that I need. The column type shows as [emp_time] [decimal](10, 0) NULL . I tried to change it to change it with:
ALTER table t_employee_detail
modify emp_time decimal (8,2)
but am getting a syntax error. What am I doing wrong?
Try
ALTER table t_employee_detail
ALTER COLUMN emp_time decimal (8,2)
ALTER table t_employee_detail
ALTER COLUMN emp_time decimal (8,2)
ASKER
Thanks much all who commented.
SELECT CAST(f7 AS DECIMAL(8,2))