Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get different in minutes between 2 Datetime Expand / Collapse
Author
Message
Posted Saturday, April 26, 2014 11:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 6, 2015 12:08 AM
Points: 75, Visits: 615
Hi,

I've table and data as follow,
declare @tAccount table
(
isLocked bit,
LastLockoutDate datetime,
currentDte dateTime
)

insert into @tAccount values('true','2014-04-26 16:11:25.337',getdate())

Let's say, current data as follow,
isLocked	| LastLockoutDate	| currentDte
1 2014-04-26 16:11:25.337 2014-04-27 01:45:15.053

How to get different in minutes between currentDate and LastLockoutDate? Mean, currentDate - LastLockoutDate.

Really need help
Post #1565347
Posted Saturday, April 26, 2014 11:57 AM This worked for the OP Answer marked as solution
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 4,166, Visits: 10,724
The DATEDIFF function is your friend here

SELECT 
isLocked
,LastLockoutDate
,currentDte
,DATEDIFF(MINUTE,LastLockoutDate,currentDte) AS DIFF_MINUTE
FROM @tAccount

Result
isLocked LastLockoutDate         currentDte              DIFF_MINUTE
-------- ----------------------- ----------------------- -----------
1 2014-04-26 16:11:25.337 2014-04-26 18:57:15.777 166
Post #1565348
Posted Saturday, April 26, 2014 12:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 6, 2015 12:08 AM
Points: 75, Visits: 615
tq
Post #1565349
Posted Saturday, April 26, 2014 12:07 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 5,036, Visits: 11,562
Note that it won't return complete minutes (or any other period) and will count for the times the period changes.
In the following example both calculations return 1 and that is expected.
SELECT DATEDIFF(MI, '20140426 01:00:59.997', '20140426 01:01:00.000'),
DATEDIFF(MI, '20140426 01:00:00.000', '20140426 01:01:59.997')




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565350
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse