SQL2005 - Counting Hours and Minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL2005 - Counting Hours and Minutes

Author

Topic

skipfeeney
Starting Member

2 Posts

Posted - 2011-11-11 : 15:24:30

Hi,

I am developing an online App to allow users to submit monthly Overtime claims.

They will simply be inputting in the total hours and minutes overtime they have done. So it could be 35:30 or 00:25 for example. I cant work out how to store this in SQL - what data type do i use?

I will also need to do some calculations in the future with Reporting Services - eg calculate Overtime over a 3 month period.

I am completely stumped on how to do this due to the complications of time, so any help would be much appreciated.

Thanks

Skip

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-11-11 : 15:44:39

"35:30" seems to me to be a presentation of time, not a data type to be stored. The data you need to store is the granular metric-- probably minutes? Convert 35:30 to (35*60)+30 and store the 2130 minutes of overtime. You can always convert it back to 35:30 to present. Also, storing it as an int will allow you to use native functions on the stored data (avg, sum, etc).

Another strategy would be to store the two datetimes rather than ask the user to calculate the overtime for you. If you, for example, knew the worker was on the job from StartDate='1/1/2011 8:00:00' --> EndDate='1/1/2011 19:00' then you could calculate the overtime as anything > 8hours, or whatever you deem "over." Might be useful to not preconceive what might constitute over and instead just store the raw date (Start and End dates). Leave the logic decisions until the last possible moment, and make those decisions in the biz layer :)

Nathan Skerl

skipfeeney
Starting Member

2 Posts

Posted - 2011-11-11 : 17:13:05

Thanks for the advice - I think converting it to minutes is the best option. However how i do convert it back? ie 2130 back to 35hrs £0 minutes?

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-11-11 : 17:46:26

Ideally you would do this sort of logic in the presentation layer, but the idea is :


declare @mins int;
set @mins = 2130;

select	cast(@mins/60 as varchar) + ':' + cast(@mins%60 as varchar)

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-11-11 : 17:47:26

also, ref this link on a more robust function to do the conversion:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:31:19

quote:
Originally posted by skipfeeney

Thanks for the advice - I think converting it to minutes is the best option. However how i do convert it back? ie 2130 back to 35hrs £0 minutes?


SELECT CAST(2130/60 AS varchar(10)) + ':' + CAST(2130%60 AS varchar(10)) AS timeinhourminutes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:32:20

and below is way by which you can get difference between two dates in hh:mm:ss format (if you choose to store start and end dates)

http://visakhm.blogspot.com/2010/03/time-difference-function.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources