Getting Time gap in day

Question

Post reply

Getting Time gap in day

ns.sharath

SSC Eights!

Points: 876
More actions
November 8, 2010 at 11:49 pm

#231318

I have a requirement wherein there are 2 columns : In Time and Out Time. I need to get the time gap in day between in time and out time.
For eg: if In time is 2010-11-09 02:01:00 and Out time in 2010-11-08 02:45:00 then I need to know the time fram is between 2-3? Is there any method of doing this other than creating a temp table and storing hours and then getting the time gap?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

select DATEDIFF(hour,'2010-10-08 23:59:59','2010-10-09 03:00:00')

??

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

ns.sharath SSC Eights! Points: 876 More actions · Answer 2

Thnks for the response.

Actually I did not want the hour diffrence, I wanted the time for which the employee was in? ie in this case from 12 to 3 the person is in.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

If this isn't what you want, then please read the link in my signature.

(note: there may be a better way of doing this)

DECLARE @TABLE AS TABLE(

ID INT IDENTITY,

InTime DATETIME,

OutTime DATETIME)

INSERT INTO @TABLE

SELECT '2010-11-09 02:15:00', '2010-11-09 02:45:00'

UNION ALL SELECT '2010-11-09 01:01:00', '2010-11-09 01:45:00'

UNION ALL SELECT '2010-11-08 00:01:00', '2010-11-09 12:45:00'

SELECT id,

RIGHT('0' + CAST(Datepart(HOUR, Dateadd(HOUR, Datediff(HOUR, '20000101',Dateadd(MINUTE, 30,intime)),'20000101')) AS NVARCHAR(2)), 2)

+ ':' +

RIGHT('0' + CAST(Datepart(MINUTE, Dateadd(HOUR,Datediff(HOUR, '20000101',Dateadd(MINUTE,30, intime)),'20000101')) AS NVARCHAR(2)), 2)

+ ' - ' +

RIGHT('0' + CAST(Datepart(HOUR, Dateadd(HOUR, Datediff(HOUR, '20000101',Dateadd(MINUTE, 30, outtime)),'20000101')) AS NVARCHAR(2)), 2)

+ ':' +

RIGHT('0' + CAST(Datepart(MINUTE,Dateadd(HOUR,Datediff(HOUR,'20000101',Dateadd(MINUTE,30,outtime)),'20000101')) AS NVARCHAR(2)), 2)

AS TimeIn

FROM @TABLE

Output: -

/*

id TimeIn

----------- -------------

1 02:00 - 03:00

2 01:00 - 02:00

3 00:00 - 13:00

*/

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dbuendiab SSC Eights! Points: 993 More actions · Answer 4

More compact query:

[font="Courier New"]DECLARE @TABLE TABLE(

ID INT IDENTITY,

InTime DATETIME,

OutTime DATETIME)

INSERT INTO @TABLE

SELECT '2010-11-09 02:15:00', '2010-11-09 02:45:00'

UNION ALL SELECT '2010-11-09 01:01:00', '2010-11-09 01:45:00'

UNION ALL SELECT '2010-11-08 00:01:00', '2010-11-09 12:45:00'

SELECT id,

Convert( varchar(3), intime, 108 ) + '00' +

' - ' +

Convert( varchar(3), DateAdd( hour, 1, outtime ), 108 ) + '00'

FROM @TABLE[/font]