Computed Column Formula - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Computed Column Formula

Author

Topic

Cre8tivedaze
Starting Member

8 Posts

Posted - 2012-02-10 : 07:35:42

Hi all,

I'm hoping the collective genius can help me out here. I'm in the midst of writing a timesheet application and I've found myself needing to use computed column formulas in SQL but I'm a complete newbie to it so I'm not sure if even what I want to do is possible.

Alright, my table has 3 columns 'startTime', 'endTime' & 'lunchBreak' and they are all of type Time(7). Now, I have a fourth column called 'TotalHours' which I basically want to compute the startTime minus the endTime minus the lunchBreak columns but I want it expressed as a decimal such as 7.15 or 7.30.

I've managed to figure out this much so far:

(datediff(minute,[startTime],[endTime])/(60.0))

Obviously this only gives me the difference between the startTime & endTime and not the lunchBreak as well.

Any help anyone can provide would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-10 : 08:52:16

( datediff(minute, startTime, endTime) - LunchBreak ) / 60E

N 56°04'39.26"
E 12°55'05.63"

Cre8tivedaze
Starting Member

8 Posts

Posted - 2012-02-10 : 09:38:19

[quote]Originally posted by SwePeso

( datediff(minute, startTime, endTime) - LunchBreak ) / 60E

Hi SwePeso

I tried your suggestion but I get the following error:

Msg 206, Level 16, State 2, Line 2
Operand type clash: time is incompatible with int

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:42:27

is it this then?
( datediff(minute, startTime, endTime) - dateadd(minute,0,LunchBreak) ) / 60E

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

Cre8tivedaze
Starting Member

8 Posts

Posted - 2012-02-10 : 09:47:08

Same error message visakh16.

Msg 206, Level 16, State 2, Line 2
Operand type clash: time is incompatible with int

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 09:59:28

quote:
Originally posted by Cre8tivedaze

Similar error message.

Msg 206, Level 16, State 2, Line 2
Operand type clash: time is incompatible with int

( datediff(minute, startTime, endTime) - dateadd(minute,'00:00',LunchBreak) ) / 60E

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

Cre8tivedaze
Starting Member

8 Posts

Posted - 2012-02-10 : 10:01:11

I tried your new suggestion visakh16 but now I get the following error message

Msg 8116, Level 16, State 1, Line 2
Argument data type varchar is invalid for argument 2 of dateadd function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:08:14

quote:
Originally posted by Cre8tivedaze

I tried your new suggestion visakh16 but now I get the following error message

Msg 8116, Level 16, State 1, Line 2
Argument data type varchar is invalid for argument 2 of dateadd function.

ah sorry it was datediff


( datediff(minute, startTime, endTime) - datediff(minute,'00:00',LunchBreak) ) / 60E

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

Cre8tivedaze
Starting Member

8 Posts

Posted - 2012-02-10 : 10:12:53

Champion. That did the trick. Thanks for your help - it was greatly appreciated visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 10:25:43

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources