| Author |
Topic |
|
sanjay_hartford
Starting Member
7 Posts |
 Posted - 2013-04-03 : 12:50:22
|
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
5072 Posts |
Posted - 2013-04-03 : 12:53:28
|
| What data type is TS.LAST_TIMESTMP? |
 |
|
|
sanjay_hartford
Starting Member
7 Posts |
Posted - 2013-04-03 : 13:01:40
|
| it is of timestamp. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-03 : 13:32:55
|
SELECT DateDiff(minute,LAST_TIMESTMP ,CURRENT_TIMESTAMP) FROM TableName
Cheers
MIK |
|
|
|
sanjay_hartford
Starting Member
7 Posts |
Posted - 2013-04-03 : 13:38:43
|
Thanks Mik. I was trying below query
select LAST_TIMESTMP, current_timestamp, DateDiff(minute,LAST_TIMESTMP ,CURRENT_TIMESTAMP) from
CAQH_O.transaction_status
where
transactionreferenceid='20121114161324042270_000545';
but it is complaning about DateDiff function saying, "DATEDIFF": invalid identifier" . Does it mean, it is not recognizing this function.
suggest me.
Thanks, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 13:50:17
|
are you using sql server? DATEDIFF is a function in SQL Server
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
sanjay_hartford
Starting Member
7 Posts |
Posted - 2013-04-03 : 14:03:59
|
| I am trying to run in Oracle SQL Developer 2005. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 14:21:22
|
then datediff wont work. see documentattion for corresponding function in Oracle.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
sanjay_hartford
Starting Member
7 Posts |
Posted - 2013-04-03 : 14:34:23
|
| any links, where I could see the same? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-03 : 14:42:07
|
try in some oracle forums. This is ms sql server forum
www.orafaq.com
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
difference between two timestamps in minutes