CODEWITHSUNDEEP
sql-server
rayncorg
rayncorg

Reputation: 993

Get Exact calculated total Hours and minutes in sql server

Good day. Im using ms sql server 2005 express and heres my table structure:

      User  |        Log_Date         |     Log_In_Time         |       Log_Out_Time      |
-----------------------------------------------------------------------------------------
    xxx     | 2013-10-02 23:00:31.623 | 2013-10-02 23:07:31.623 | 2013-10-03 01:08:31.623 |

NOTE:

LOG_OUT_TIME is in other day which is October 3, 1:08 AM, LOG_IN_TIME is October 2, 11:00 PM

Now I want to add column: TOTAL

and it look like this:

      User  |        Log_Date         |     Log_In_Time         |       Log_Out_Time      | TOTAL  |
---------------------------------------------------------------------------------------------------
    xxx     | 2013-10-02 23:00:31.623 | 2013-10-02 23:07:31.623 | 2013-10-03 01:08:31.623 | 2.08   |

Can anybody know the way?

Thank you in advance!

Upvotes: 0

Views: 3998

Answers (4)

bendataclear
bendataclear

Reputation: 3850

Very simple way:

CAST(Log_Out_Time - Log_In_Time AS TIME)

Gives:

02:01:00.0000000

Edit:

Exact query:

DECLARE @ST DATETIME = '2013-10-02 23:07:31.623'
DECLARE @ET DATETIME = '2013-10-03 01:08:31.623'

SELECT CAST(@ET - @ST AS TIME)

Upvotes: 2

Vineeth Vijayan
Vineeth Vijayan

Reputation: 1329

This will work:

SELECT CAST(DATEDIFF(hour, Log_In_Time, Log_Out_Time) AS varchar) + '.' + 
    CAST(DATEDIFF(mi, Log_In_Time, Log_Out_Time) / (60 * DATEDIFF(hour, Log_In_Time, Log_Out_Time) ) AS varchar)
FROM tableName

I have calculated the hours and minutes separately.

Upvotes: 1

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

select 
cast(datepart(hour,Log_Out_Time - Log_Date) as varchar) + '.' +  
right('00' + cast(datepart(minute,Log_Out_Time - Log_Date) as varchar),2)

Upvotes: 0

Steve Tighe
Steve Tighe

Reputation: 96

You could use DateDiff @Unknownymous, what I would do is seconds comparison, cast this to a float and the divide by 60 as necessary. This will give you minutes, hours, etc scaling up to the precision you need.

DECLARE @diff float
DECLARE @diffMin float
DECLARE @diffHour float
DECLARE @diffDay float

SELECT @diff = cast(datediff(ss,Log_in_Time,Log_Out_Time) as FLOAT)
SELECT @diffMin = @diff/60
SELECT @diffHour = @diffMin/60
SELECT @diffDay = @diffHour/24

print @diff
print @diffmin
print @diffhour
print @diffday

Upvotes: 0

Related Questions