Post reply

Getting Time gap in day

  • 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?

  • November 9, 2010 at 12:07 am

    #1248034

    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

  • November 9, 2010 at 12:30 am

    #1248044

    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.

  • November 9, 2010 at 5:37 am

    #1248172

    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/

  • November 9, 2010 at 7:39 am

    #1248294

    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]

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

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