time difference - 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

General SQL Server Forums

New to SQL Server Programming

time difference

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 07:18:40

I am using sql server 2008

I have a table with following fields

empno timein timeout workedfor

1 07:59:00 18:22:00

i want the time difference (i.e workedfor) for emp1 in integer format that is 10.23

Sachin.Nand

2937 Posts

Posted - 2010-12-04 : 07:34:56

[code]
declare @dt1 datetime='07:59:00'
declare @dt2 datetime='18:22:05'

select DATEDIFF(mi,@dt1,@dt2)/60 HRS,
DATEDIFF(MINUTE,@dt1,@dt2)%60 MINS,
DATEDIFF(SECOND,@dt1,@dt2)%60 SECS

[/code]

PBUH

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 07:42:26

how i get the answer in integer and insert it to the new coloumn.

that is 10.23 should be entered to column workedfor

Sachin.Nand

2937 Posts

Posted - 2010-12-04 : 07:56:00

10.23 is NOT an integer.

PBUH

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 08:07:00

i want the answer in integer so that later on i can sum it

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:07:25

You can enter the data in form of minutes for the column workedfor
that is 623 Minutes in this case.

Vaibhav T

If I cant go back, I want to go fast...

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 08:11:52

it can't be changed to hr.min that is 10.23

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:13:46

Then you need to take datatype as varchar or Numeric.
But for time Numeric DataType is not recomended.

Vaibhav T

If I cant go back, I want to go fast...

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 08:21:02

then can i sum varchar. to get the total time when i am generating report

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:27:55

Why you want the column for Workedfor As You have InTime and OutTime.
Where ever you need to display it do the calculation there.

Vaibhav T

If I cant go back, I want to go fast...

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-04 : 08:38:19

As i need to create a report for each employee.

suppose

empno entry date timein timeout timediff
1 04/12/2010 08:07 18:00
1 04/12/2010 08:10 18:30
1 04/12/2010 08:00 18:44
1 04/12/2010 08:12 18:45
1 04/12/2010 08:05 18:13

There can be lot of employee in the table

for each employee i want to show total hr worked in hr/ min format

say 10:30 if it will be in integer say 10.30 will be easy for me sum the total hr worked

this is what i am thinking, but you have said i am not able to understand sorry for that
can you explain it again referring to this problem

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:48:08

See the reply of Sachin.Nand

First understand the datatype
Integer is whole value without decimal
10.30 can not be stored in integer datatype.
If anyway you want to store the data in integer format

then you can do one thing
Store hour and minute in different columns
say HRworkedfor and MinWorkedFor

But its a very bad desing.

Time 10.23 is different from decimal number 10.23
Because time can not be 10.75 but decimal can be.

Moreover when you will sum the time then 10 hr 45 min & 11 hr 45 min
will result as 21 hr 90 min if you are doing sum saperately.
rather it should be 22 hr 30 mins.

I hope it was clearly explained.

Vaibhav T

If I cant go back, I want to go fast...

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:57:31

You can go with the my suggetion
to store total minutes in workedFor Column.
so that you dont need to change column data type

and will be easy to sum and convert if in HH:MM format
where ever you want.

for example -
when you will sum 623 mins (10 hr 23 mins)
with 652 mins (10 hr 52 mins )
it will be 1284 mins
which can be easily converted into hh:mm format
Like below

DECLARE @i AS INT
SET @i = 1284

SELECT @i/60, @i%60

that is 21 hr 24 mins

Vaibhav T

If I cant go back, I want to go fast...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-04 : 09:08:55

Here is one way, if the time columns are datetime..

Note the first query returns the row level total hours, then for demonstration that result is the subquery used in the 2nd query.


Create table #foo (empno int not null, TimeIn datetime not null, TimeOut datetime not null)

Insert into #foo
Select 1, '20100412 08:07', '20100412 18:00' UNION
Select 2, '20100412 08:10', '20100412 18:30' UNION
Select 1, '20100413 08:00', '20100413 18:15'

Select empno
	,Convert(varchar(10),timein,110) as DateIn
	,CONVERT(varchar(8),timein, 108) as TimeIn
	,CONVERT(varchar(8),timeout,108) as TimeOut
	,CONVERT(varchar(8),(timeout-timein),108) as HoursWorked

FROM #Foo

Select empno,SUM(Substring(HoursWorked,4,2)*60 + substring(HoursWorked,1,2)*3600.00)/3600.00
FROM (Select empno
	,Convert(varchar(10),timein,110) as DateIn
	,CONVERT(varchar(8),timein, 108) as TimeIn
	,CONVERT(varchar(8),timeout,108) as TimeOut
	,CONVERT(varchar(8),(timeout-timein),108) as HoursWorked FROM #foo) a
GROUP BY empno

Drop table #foo

Poor planning on your part does not constitute an emergency on my part.

Sachin.Nand

2937 Posts

Posted - 2010-12-05 : 11:47:54

[code]

Create table #foo (empno int not null, TimeIn datetime not null, TimeOut datetime not null)

Insert into #foo
Select 1, '20100412 08:07', '20100412 18:00' UNION
Select 2, '20100412 08:10', '20100412 18:30' UNION
Select 1, '20100413 08:00', '20100413 18:15'

select empno,sum(InMins)/60 Hrs, RIGHT('0' + convert(varchar(2),SUM(InMins)%60),2)Mins from
(
select empno,DATEDIFF(mi,timein,timeout)InMins from #foo

)t group by empno
drop table #foo
[/code]

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-11 : 07:48:47

SELECT REPLACE(CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, EmpSin, EmpSout), 0), 108), ':', '.')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources