SQLTeam.com | Weblogs | Forums

How can we convert 1hour 90 minutes to 2 hours 30 minutes

Please help us to 'How can we convert 1hour 90 minutes(1.90) to 2 hours 30 minutes' through sql.

I would have thought 1.90 is more likely to be 1 hour 54 minutes:

DECLARE @value decimal(5,2) = 1.9;
SELECT CAST(dateadd(minute, (60 * @value), 0) AS time);

If you want 2 hours 30 minutes:

DECLARE @value decimal(5,2) = 1.9;
SELECT CAST(DATEADD(minute, @value%1 * 100, DATEADD(hour, FLOOR(@value), 0)) AS time);
1 Like

hi

this is another way of doing it
hope this helps :slight_smile: :slight_smile:

declare @time decimal(10,2) = 1.90
select CONVERT(varchar, DATEADD(ms, 3600.0*@time * 1000, 0), 114)

image

1 Like

What data type would the final result be? Varchar, datetime, ?

it is ... VARCHAR .. :slight_smile: for what i did

Hi Harish,

Thanks for reply. Actually am getting 1hour, 90minutes(1.90) in duration field. But according to my requirement i need to convert 2hours, 30 minutes(2.30).

Hi Harish,

when am trying to execute the following one am getting 'ORA-00911: invalid character'
SELECT CAST(DATEADD(minute, 1.90%1 * 100, DATEADD(hour, FLOOR(1.90), 0)) AS time) from dual;

Please help me on this

hi ram

i am using Microsoft SQL server t-sql
looks like you are in Oracle ...

i dont have oracle installed

please try to google
it maybe % or * that may be causing the issue ..!!!!

1 Like

hi ram

sorry for the late reply

i have done it this way ... hope it helps :slight_smile: :slight_smile:
please correct me and let me know if i am missing anything
i am using Microsoft SQL Server T-SQL

declare @time decimal(10,2) = 1.90
select cast(@time as int) + PARSENAME(@time,1)/60 ,  PARSENAME(@time,1)%60

image

1 Like

Thanks for helping...It is working now.