SQL Stored Procedure - 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 2000 Forums

Transact-SQL (2000)

SQL Stored Procedure

Author

Topic

hisouka
Starting Member

28 Posts

Posted - 2007-06-25 : 04:02:16

How can i compute Total Hours? of these table? TotalHours must be twodecimal places for ex(10.23)

EmpNo......Date......TimeIn.....TimeOut.......TotalHours
123......1/1/2007.....7:00.......14:00............?
124......1/1/2007.....8:33.......15:34............?
123......1/2/2007.....10:30......20:58............?
124......1/2/2007.....7:06.......15:36............?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 04:12:17

What is the data type for the TimeIn and TimeOut columns?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hisouka
Starting Member

28 Posts

Posted - 2007-06-25 : 04:23:15

Sir, only varchar(50)

tnx

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-25 : 04:40:10

SELECT EmpNo, Date, TimeIn, TimeOut,
CONVERT(numeric(4, 2), CONVERT(numeric(4, 2), LEFT(TimeOut, CHARINDEX(':', TimeOut)- 1))
+ CONVERT(numeric(4, 2), RIGHT(TimeOut, LEN(TimeOut) - CHARINDEX(':', TimeOut))) / 60
- CONVERT(numeric(4, 2), LEFT(TimeIn, CHARINDEX(':', TimeIn)- 1))
- CONVERT(numeric(4, 2), RIGHT(TimeIn, LEN(TimeIN) - CHARINDEX(':', TimeIn))) / 60)
FROM YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-25 : 04:51:14

What is the datatype od date column?
If you used datetime column for both timein and timeout, it is just using datediff function over them

Datediff(hour,timein,timeout)

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 04:59:05

[code]-- prepare sample data
declare @t table
(
EmpNo int,
Date datetime,
TimeIn varchar(50),
TimeOut varchar(50)
)

insert @t
select 123,'1/1/2007','7:00','14:00' union all
select 124,'1/1/2007','8:33','15:34' union all
select 123,'1/2/2007','10:30','20:58' union all
select 124,'1/2/2007','7:06','15:36'

-- desired query
select
EmpNo,
[Date],
TimeIn,
TimeOut,
replace(convert(varchar(5), cast(TimeOut as datetime) - Cast(TimeIn as datetime), 108), ':', '.') as TotalHours
From
@t[/code]

Note: This is purely based on the sample data you posted. It will not work if time difference spans across dates.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hisouka
Starting Member

28 Posts

Posted - 2007-06-25 : 05:13:28

Hi harsh_athalye,

Thanks. it works.. But how can i conver 09.42 result to 9.42 only? based on ur SP.. thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-25 : 05:18:49

Just convert it to Numeric(25,2):

-- prepare sample data
declare @t table
(
	EmpNo int,
	Date datetime,
	TimeIn varchar(50),
	TimeOut varchar(50)
)

insert @t
select 123,'1/1/2007','7:00','14:00' union all
select 124,'1/1/2007','8:33','15:34' union all
select 123,'1/2/2007','10:30','20:58' union all
select 124,'1/2/2007','7:06','15:36'

-- desired query
select 
	EmpNo,
	[Date],
	TimeIn,
	TimeOut,
	convert(numeric(25,2), replace(convert(varchar(5), cast(TimeOut as datetime) - Cast(TimeIn as datetime), 108), ':', '.')) as TotalHours
From
	@t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hisouka
Starting Member

28 Posts

Posted - 2007-06-25 : 05:48:37

Thanks very much..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources