How can I find the difference between two date fields in separate tables and different formats?

Posted by Jkopy1962 under Sql Server on 12/5/2014 | Points: 10 | Views : 1033 | Status : [Member] | Replies : 3
I have one table that has an alarm generation date in the format MM-DD-YYYY HH:MM:SS AM and a Call start field in another table with the format YYYY-MM-DD HH:MM:SS. I've used the very basic query below to get the raw information. How do I get the difference between the two fields as they are in different formats?


select p.alarm_generation_dt, o.call_start
from phm_interaction_pers p, phm_outbound o
where o.call_start like '2014-12-03%'




Responses

Posted by: Bandi on: 12/8/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
select p.alarm_generation_dt, o.call_start , datediff( mi, o.call_start, p.alarm_generation_dt) DiffInMinutes
from phm_interaction_pers p, phm_outbound o
where o.call_start like '2014-12-03%'


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jkopy1962, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jkopy1962 on: 12/8/2014 [Member] Starter | Points: 25

Up
0
Down
Thank you very much for your response, Chandu. I ran into a problem, however. I get the following error:

ORA-00904: "DATEDIFF": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 45

I am using Oracle SQL Developer Version 3.2.20.09 Build MAIN-09.87

Jkopy1962, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/8/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
As per error message, you are working with ORACLE database, but not the SQL Server...

In Oracle, try the below code....

SELECT	TO_DATE(p.alarm_generation_dt, 'YYYYMMDD HH:MI:SS AM')
- TO_DATE(o.call_start, 'YYYYMMDD HH:MI:SS AM') day_diff
from phm_interaction_pers p, phm_outbound o
where o.call_start like '2014-12-03%'


For your reference,
http://oracletuts.net/sql/how-to-calculate-difference-between-dates-in-oracle-sql/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jkopy1962, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response