We help IT Professionals succeed at work.

ASP.net C# SQL convert time into general number

929 Views
Last Modified: 2012-05-05
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
Comment
Watch Question

Applications and Integrations Consultan
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
TimCotteeApplications and Integrations Consultan

Commented:
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
TimCotteeApplications and Integrations Consultan

Commented:
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

Author

Commented:
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

Author

Commented:
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.



Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.