SQL Server How do I subtract two datetime values?

iaqfqrcu  于 2023-04-04  发布在  其他
关注(0)|答案(5)|浏览(124)

I have a table with two columns login and logout . If a row has values "2019-08-07 20:37:12" in login column and "2019-08-07 21:14:16" in logout column, I want the difference between time values from login and logout columns.

I have:

SELECT logintime,CONVERT(varchar(6),DATEDIFF(second, login, logout)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2),(DATEDIFF(second, login,logout) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2),DATEDIFF(second, login, logout) % 60), 2) 
AS 'HH:MM:SS' from face_login_logout

Expected result is '2019-08-07 01:23:04 ' .

am46iovg

am46iovg1#

The easiest way for your example:

SELECT TO_TIMESTAMP('2019-08-07 21:14:16','YYYY-MM-DD HH24:MI:SS') - TO_TIMESTAMP('2019-08-07 20:37:12','YYYY-MM-DD HH24:MI:SS')

But it gives 00:37:04 as a result.

SELECT TO_TIMESTAMP(logout,'YYYY-MM-DD HH24:MI:SS') - TO_TIMESTAMP(login,'YYYY-MM-DD HH24:MI:SS')
pu82cl6c

pu82cl6c2#

As @lypskee has suggested, I think your calculations are out.

I found the method below (which looks to be the same as your code) and it tells me that there is a difference of 0:37:04

DECLARE @START_DATE DATETIME
DECLARE @END_DATE   DATETIME
SET     @START_DATE = '2019-08-07 20:37:12'
SET     @END_DATE =   '2019-08-07 21:14:16'

SELECT    CONVERT(varchar(6), DATEDIFF(second, @START_DATE, @END_DATE)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, @START_DATE, @END_DATE) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, @START_DATE, @END_DATE) % 60), 2) AS 'HH:MM:SS'

update - adjusted for pulling from a table.

SELECT login, logout, CONVERT(varchar(6), DATEDIFF(second, login, logout)/3600)
    + ':'
    + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, login, logout) % 3600) / 60), 2)
    + ':'
    + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, login, logout) % 60), 2) AS 'difference'

FROM tbl_name

This would give you the result for each row in your table...you can adjust the query for any null values too.

Output:

login                |  logout               |  difference
-----------------------------------------------------------
2019-08-07 20:37:12  |  2019-08-07 21:14:16  |  0:37:04
v8wbuo2f

v8wbuo2f3#

DECLARE @logintime DATETIME='2019-08-07 20:37:12'
DECLARE @logouttime DATETIME='2019-08-07 21:14:16'

SELECT CAST(CAST(@logouttime AS DATE) AS VARCHAR(20))+' '+
   CAST(ABS(DATEPART(HOUR,@logouttime)-DATEPART(HOUR,@logintime)) AS VARCHAR(20))+':'+
   CAST(ABS(DATEPART(MINUTE,@logouttime)-DATEPART(MINUTE,@logintime)) AS VARCHAR(20))+':'+
   CAST(ABS(DATEPART(SECOND,@logouttime)-DATEPART(SECOND,@logintime)) AS VARCHAR(20))
fae0ux8s

fae0ux8s4#

For SQL Server, if you're sure that the difference is less than 24 hours, you can use timefromparts to get the difference as a time data type. I've also used cross apply to only write the datediff once instead of three times.

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE face_login_logout
(
    login datetime,
    logout datetime
)

INSERT INTO face_login_logout (login, logout) VALUES
('2019-08-07T20:37:12','2019-08-07T21:14:16')

The query:

SELECT  login, 
        logout, 
        TIMEFROMPARTS(
            Difference / 3600, 
            (Difference % 3600) / 60, 
            Difference % 60, 
            0,
            0 
        ) As TimeDiff
FROM face_login_logout
CROSS APPLY (SELECT DATEDIFF(SECOND, login, logout) As Difference) c

Results:

login                   logout                  TimeDiff
2019-08-07 20:37:12     2019-08-07 21:14:16     00:37:04

If you want a datetime as result, simply use datetimefromparts

vktxenjb

vktxenjb5#

Another easy way to subtract (or add) two datetime values in SQL Server is:

DECLARE @login  datetime = '2019-08-07 20:37:12';
DECLARE @logout datetime = '2019-08-07 21:14:16';

SELECT @logout - @login AS ElapsedTime;
ElapsedTime
-----------------------
1900-01-01 00:37:04.000

(1 row affected)

相关问题