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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:53 AM
Points: 63, Visits: 563
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,766, Visits: 4,621
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
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:53 AM
Points: 63, Visits: 563
tq
Post #1565349
Posted Saturday, April 26, 2014 12:07 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,545, Visits: 7,659
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.
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