# 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?

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

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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.