SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 difference in two timestamps in minutes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sanjay_hartford
Starting Member

7 Posts

Posted - 04/03/2013 :  12:51:05  Show Profile  Reply with Quote
hi Friends,

I need to find the difference in two timestamps and covert the difference in minutes. Once that is done, I need to check, if that difference in greater than 15 minutes or not.

I think, I would need to move the difference to a number so that I can compare it with 15. I am not sure.

Table A has a column Last_Timestamp. Another timestamp is CURRENT_TIMESTAMP. I am trying below things - >

A) IF (SELECT 24*60*(CURRENT_TIMESTAMP - Last_Timestamp)
From TransactionStatus TS
Where TS.TransactionReferenceID= Key) > 15


B) SELECT (1440 * (TS.LAST_TIMESTMP - CURRENT_TIMESTAMP)) INTO emailTimeDifference FROM CAQH_O.transaction_status TS WHERE TS.transactionreferenceid = variableKey.EMAILKEY;
IF (emailTimeDifference > 15) then

I get error as "inconsistant data type.


Please suggest the answers. I appriciate the help.

Thanks.

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  13:00:19  Show Profile  Visit russell's Homepage  Reply with Quote
Duplicate post. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184312

Please post your question only once.
Go to Top of Page

sanjay_hartford
Starting Member

7 Posts

Posted - 04/03/2013 :  13:10:07  Show Profile  Reply with Quote
yes, I just thought, If I can get reply faster. I need to complete this today. will keep in mind.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 04/03/2013 :  20:50:55  Show Profile  Reply with Quote
Declare @TimeA DateTime
Declare @TimeB DateTime

SET @TimeA = (Select '4/4/2013 17:00:00')
SET @TimeB = (Select '4/4/2013 17:16:00')


Select *, Case When [TimeDifference (minutes)] > 15 Then 'Yes' Else 'No' End [Over15Minutes]
From (
Select DATEDIFF(MINUTE, @TimeA, @TimeB) As [TimeDifference (minutes)]) d

Edited by - harlingtonthewizard on 04/03/2013 20:56:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 04/04/2013 :  01:17:22  Show Profile  Reply with Quote
quote:
Originally posted by harlingtonthewizard

Declare @TimeA DateTime
Declare @TimeB DateTime

SET @TimeA = (Select '4/4/2013 17:00:00')
SET @TimeB = (Select '4/4/2013 17:16:00')


Select *, Case When [TimeDifference (minutes)] > 15 Then 'Yes' Else 'No' End [Over15Minutes]
From (
Select DATEDIFF(MINUTE, @TimeA, @TimeB) As [TimeDifference (minutes)]) d



Please try to pass date values in unambigous format

see reason here

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000