# Converting Minutes to Decimal in SQL

I have a decimal field in my database with two decimal places which is stored with hours and then minutes, so 30 hours and 46 minutes looks like 30.46. I need to perform some calculations in my sql statement using the decimal and I need to convert the minutes only into their decimal format, so .46 to .767, with the scope at a max of 3 and rounding up. So after it is said and done it should be 30.767, but need a formula to work for any hour.minute combination. Is there a good way of doing this?

Thanks
###### 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:
declare @a decimal(5,2)

set @a = 36.47

SELECT ROUND(@a, 0) + ((@a * 100) % 100) / 60
Data EngineerCommented:
Correcting tigin's query to limit the decimal portion to 3 decimals.
``````declare @a decimal(5,2)
set @a = 30.46
SELECT ROUND(@a, 0) + Convert(decimal(5,3),((@a * 100) % 100) / 60.0)
``````

Experts Exchange Solution brought to you by

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:
Hi,
Try this :

declare @dt datetime,@mm decimal(10,2),@hr decimal(10,2)

set @dt = GETDATE()
set @mm = datepart(MINUTE,@dt)
set @hr = DATEPART(hour,@dt)
select @hr,@mm,@hr+ cast((@mm/100) as decimal(10,2))

and in just one line having replace the @dt variable by your date field or date :

select CAST(datepart(hour,@dt) as decimal(10,2)) + CAST(datepart(minute,@dt) as decimal(10,2))/100

Hope this help ;)
Commented:
yes don't hold it in that format in the first place....

either

1) store it as integer seconds and convert to display
2) store it as a datetime/time value and use the date arithmetic functions....

if you persist in storing it in this bastard format, then process it by converting it to/from seconds and then perform