asked on

ASP.net C# SQL convert time into general number

Hello,

In excel, I can take a finish time (15:45) and subtract the start time (11:00), format the cell as a "general" type and come up with 0.197916667 which I can then multiply by 24 to get 4.75.

I have done the subtraction in sql to get the time 4:45, but want to display that as 4.75 in my asp.net/c# page.

How can I do that? When I try the different number formats it does not work. I also can't seem to get any further than 4:45 in sql either, where I would be happy to complete the formula if I could.

THanks,
Bonnie

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bonnie_K

ASKER

Hi Tim,

When I enter the formula as you have it, it works fine, but when I add my field name Hours (datetime) instead of '4:45' like this:

CAST(DATEDIFF(minute, 0, CAST(Hours AS datetime)) AS decimal(5 , 2)) / 60

or like this

CAST(DATEDIFF(minute, 0, Hours) AS decimal(5 , 2)) / 60

I get the error:

Arithmatic overflow error converting int to data type numeric.

I am creating a view and the field Hours is a calculated field with the formula Finish_Time-Start_Time-NP_Time.
But I also tried the above formula with my field start_time only which is a smalldatetime and I got the same error.

Thanks,
Bonnie

TimCottee

Bonnie_K,

It is an odd thing but any time value greater than 16:39 causes this with Decimal(5,2) if you switch to casting it to decimal(6,2) then it will accept all the time values ok. Not quite sure why this would be (can't work out the conversions in my head at the moment!)

TimCottee

TimCottee

Bonnie_K,

That's it of course, 16:40 equates to 1000 minutes which overflows decimal(5,2) as it is more than 33 digits before the decimal point. decimal(6,2) has no problem with that so it works.

TimCottee

Bonnie_K

ASKER

Hi Tim,

for some reason, with

CAST(DATEDIFF(minute, 0, CAST(Hours AS datetime)) AS decimal(6 , 2)) / 60

I am still getting the error.

Thanks for your help,
Bonnie

Bonnie_K

ASKER

Thanks Tim, your answer led me to the solution which ended up being:

DATEDIFF(minute, Start_Time, Finish_Time) - DATEDIFF(minute, '1/1/2000', NP_Time)

I just had to edit the query adding data to the table to make all of the NP_Time's (non-productive time) date the same.