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 : 8493 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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%'

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

Latest Posts