date & time difference format

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

, +

I have a Login/Logout system that compute the total hours work of the employees. My problem is the formatting of the date&time difference for minutes.

Example:

if user logged-in 8:00 AM and logged-out 6:30 PM, the total should be 10.50 but my SQL output is 10 hrs and 30 mins. I know the formula(it should be 30mins/60 = 50) but I didn't know where should I put that Dividend(/60) on my SQL Query.(see below my SQL Code).

SQL Code:

SQL

SELECT *,CAST(Datediff(HOUR, 0, logout_time - login_time) +
         Cast(Datediff(MINUTE, 0, logout_time - login_time) -
             (Datediff(HOUR, 0, logout_time - login_time) * 60 ) AS DECIMAL) / 100 AS Decimal(18,2)) AS [TotalHrs_Worked]
FROM table_DTR
WHERE CONVERT(VARCHAR(10),log_date, 101) BETWEEN '09/01/2014' AND '11/30/2014'

Posted 14-Dec-14 22:39pm

Uknownymous

Add a Solution

3 solutions

Solution 1

check this

SQL

Declare @date1 Datetime='Dec 15 2014 08:00 AM'
Declare @date2 Datetime='Dec 15 2014 06:30 PM'

select DATEDIFF(MINUTE,@date1,@date2)/60.0

Posted 14-Dec-14 22:46pm

Shweta N Mishra

Solution 3

try..

SQL

SELECT *,CAST((Datediff(MINUTE,login_time,logout_time)/60) as decimal(18,2)) AS [TotalHrs_Worked]
    FROM table_DTR
    WHERE CONVERT(VARCHAR(10),log_date, 101) BETWEEN '09/01/2014' AND '11/30/2014'

Posted 14-Dec-14 23:17pm

/\jmot

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2014-12-14T22:48:00

Solution 2

Ignore the hours: do the difference in minutes only, and convert that to floating point hours and you will get your result.

SQL

SELECT *, CAST(DATEDIFF(minute, login_time, logout_time) AS DECIMAL) / 60 AS Worked FROM MyTable

Posted 14-Dec-14 22:48pm

OriginalGriff

Comments

Uknownymous 15-Dec-14 20:02pm

thanks for the explanation..

date & time difference format

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

date & time difference format

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us