# 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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
This worked for me, may not be the best example though.

CAST(LEFT(@dtm,2) + (CAST(SUBSTRING(@dtm,3,2) AS Numeric(8,2)) / 60) AS Numeric(8,2))

Cheers, Andrew
0
Commented:
try this

SELECT CAST(f7 AS DECIMAL(8,2))
0
Commented:
Sorry about the proposed solution, I normally only post as comments must have clicked on Proposed by accident.
Cheers, Andrew
0
Commented:
Hello ckangas7

Your query could look something like this:

INSERT INTO t_employee_detail (emp_time)
SELECT CONVERT (DECIMAL(9, 2),
CAST(SUBSTRING(f7, 1, 2) AS FLOAT) +
CAST(SUBSTRING(f7, 3, 2) AS FLOAT) / 60 +
CAST(SUBSTRING(f7, 5, 3) AS FLOAT) / 60)

Explanation:

The first thing you should do is parse the string into the parts you want. In this case, separate the hours, minutes, and seconds and convert them to numbers. This code should give you a good idea on how this could be done:

DECLARE @RawTimeData AS CHAR(7) SET @RawTimeData = '0730000'

DECLARE @Hours AS FLOAT SET @Hours = CAST(SUBSTRING(@RawTimeData, 1, 2) AS FLOAT)
DECLARE @Minutes AS FLOAT SET @Minutes = CAST(SUBSTRING(@RawTimeData, 3, 2) AS FLOAT)
DECLARE @Seconds AS FLOAT SET @Seconds = CAST(SUBSTRING(@RawTimeData, 5, 3) AS FLOAT)

SELECT CONVERT(DECIMAL(9, 2), @Hours  + (@Minutes / 60) + (@Seconds / 60))

Here are some references that may be useful:

Transact-SQL Reference on MSDN2
http://msdn2.microsoft.com/en-us/library/ms189826.aspx

SQL Data Types
http://msdn2.microsoft.com/en-us/library/ms187752.aspx

SUBSTRING
http://msdn2.microsoft.com/en-us/library/ms187748.aspx

CAST and CONVERT functions
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
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
0
Author Commented:
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?
0
Commented:
Try

ALTER table t_employee_detail
ALTER COLUMN emp_time decimal (8,2)
0
Author Commented:
Thanks much all who commented.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.