Home » SQL & PL/SQL » SQL & PL/SQL » Subtracting two dates in string format with out AM or PM (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0)
Subtracting two dates in string format with out AM or PM [message #596867] Thu, 26 September 2013 19:51 Go to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Hi All,


I have a peculiar situation where i have a column which has a string. The text has to be parsed to identify two date strings.

After converting the strings to date they have to be subtracted to obtain the time taken.

Table to create

CREATE TABLE JOB_EXECUTION 
(
RUN_DATE DATE,
RUN_ORDER NUMBER,
RUN_DIV VARCHAR2(50),
RUN_TYPE VARCHAR2(50),
RUN_FMT  VARCHAR2(50),
PICKUP_STATUS  VARCHAR2(50),
START_TIME DATE,
END_TIME DATE,
REMARKS VARCHAR2(4000)
);


Insert sample data

INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT  ,PICKUP_STATUS  ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50.
AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52.
AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57.
FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59.
ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
METRICs PROCESS: as of @ 26-Sep-2013 10:39:59.
METRIC_7 PROCESS: as of @ 26-Sep-2013 10:52:39.
METRIC_4 PROCESS: as of @ 26-Sep-2013 10:52:40.
METRIC_1 PROCESS: as of @ 26-Sep-2013 10:53:17.
METRIC_10 PROCESS: as of @ 26-Sep-2013 10:53:21.
METRIC_5 PROCESS: as of @ 26-Sep-2013 11:03:19.
METRIC_8 PROCESS: as of @ 26-Sep-2013 11:03:20.
METRIC_2 PROCESS: as of @ 26-Sep-2013 11:03:29.
METRIC_11 PROCESS: as of @ 26-Sep-2013 11:04:01.
METRIC_6 PROCESS: as of @ 26-Sep-2013 11:13:46.
METRIC_9 PROCESS: as of @ 26-Sep-2013 11:13:46.
METRIC_3 PROCESS: as of @ 26-Sep-2013 11:14:10.
METRIC_12 PROCESS: as of @ 26-Sep-2013 11:14:21.
BENCHMARKs PROCESS: as of @ 26-Sep-2013 11:14:26.
BENCHMARKs PROCESS: as of @ 26-Sep-2013 11:20:35.
REPORTING_6 PROCESS: as of @ 26-Sep-2013 11:24:47.
REPORTING_1 PROCESS: as of @ 26-Sep-2013 11:26:12.
FINISHED: as of @ 26-Sep-2013 11:26:21"');



INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT  ,PICKUP_STATUS  ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate,1,'BE','FILE-HFM','MAR_FCST-2012','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 24-Sep-2013 04:04:19.
AFM-Load END PROCESS: as of @ 24-Sep-2013 04:04:20.
AUTO-LOAD RUNNING :as of @ 24-Sep-2013 04:04:27.
FILEs-LOAD PROCESS: as of @ 24-Sep-2013 04:04:29.
ALLOCATIONs PROCESS: as of @ 24-Sep-2013 04:04:58.
METRICs PROCESS: as of @ 24-Sep-2013 04:11:22.
METRIC_4 PROCESS: as of @ 24-Sep-2013 04:13:35.
METRIC_4 PROCESS: as of @ 24-Sep-2013 04:13:35.
METRIC_10 PROCESS: as of @ 24-Sep-2013 04:13:36.
METRIC_7 PROCESS: as of @ 24-Sep-2013 04:13:59.
METRIC_5 PROCESS: as of @ 24-Sep-2013 04:15:27.
METRIC_2 PROCESS: as of @ 24-Sep-2013 04:15:27.
METRIC_11 PROCESS: as of @ 24-Sep-2013 04:15:30.
METRIC_8 PROCESS: as of @ 24-Sep-2013 04:16:15.
METRIC_3 PROCESS: as of @ 24-Sep-2013 04:17:12.
METRIC_6 PROCESS: as of @ 24-Sep-2013 04:17:13.
METRIC_12 PROCESS: as of @ 24-Sep-2013 04:17:22.
METRIC_9 PROCESS: as of @ 24-Sep-2013 04:18:09.
BENCHMARKs PROCESS: as of @ 24-Sep-2013 04:18:33.
BENCHMARKs PROCESS: as of @ 24-Sep-2013 04:20:43.
REPORTING_6 PROCESS: as of @ 24-Sep-2013 04:23:03.
REPORTING_1 PROCESS: as of @ 24-Sep-2013 04:23:06.
FINISHED: as of @ 24-Sep-2013 04:23:17"');



The select I tried.

WITH reporting_info AS (
      SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME,
      REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD,
      REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
      REPLACE(substr(substr(remarks,instr(remarks,'METRICs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'METRICs PROCESS: as of @ ')),chr(10))-2 ),'METRICs PROCESS: as of @ ','') METRIC,
      replace(substr(substr(remarks,instr(remarks,'BENCHMARKs PROCESS: as of @ ',1,2)), 0,instr(substr(remarks,instr(remarks,'BENCHMARKs PROCESS: as of @ ',1,2)),chr(10))-2 ),'BENCHMARKs PROCESS: as of @ ','') BENCHMARK,
      REPLACE(substr(remarks,instr(remarks,'FINISHED: as of @ ')),'FINISHED: as of @ ','')  REPORTING
      FROM JOB_EXECUTION b 
      WHERE B.PICKUP_STATUS = 'FINISHED'
      AND B.RUN_DIV IN ('AE','BE','PS')
      AND TRUNC(B.RUN_DATE) >= TRUNC(SYSDATE-500)
      --AND b.run_type = 'FILE-HFM'
)
SELECT R.RUN_DIV,SUBSTR(R.RUN_FMT,0,INSTR(R.RUN_FMT,'-',1)-1) AS FMT
,SUBSTR(R.RUN_FMT,INSTR(R.RUN_FMT,'-',1)+1) AS YEAR
,R.BATCH_ID,R.BATCH_START_TIME
, R.BATCH_END_TIME,
ROUND(((TO_DATE(R.ALLOCATION,'dd-Mon-yyyy hh24:mi:ss') -  TO_DATE(FILE_LOAD,'dd-mon-yyyy hh24:mi:ss') )*1440),1) UNALLOCATED,
round(((to_date(r.METRIC,'dd-Mon-yyyy hh24:mi:ss') -  to_date(ALLOCATION,'dd-mon-yyyy hh24:mi:ss') )*1440),1) ALLOCATION,
round(((to_date(r.BENCHMARK,'dd-Mon-yyyy hh24:mi:ss') -  to_date(METRIC,'dd-mon-yyyy hh24:mi:ss') )*1440),1) METRIC,
round(((to_date(r.REPORTING,'dd-Mon-yyyy hh24:mi:ss') -  to_date(BENCHMARK,'dd-mon-yyyy hh24:mi:ss') )*1440),1) REPORTING,
round(((to_date(r.REPORTING,'dd-Mon-yyyy hh24:mi:ss') -  to_date(FILE_LOAD,'dd-mon-yyyy hh24:mi:ss') )*1440),1) OVER_ALL_TIME_TAKEN*/
from reporting_info r
WHERE 
R.RUN_TYPE = 'FILE-HFM'
AND r.benchmark NOT LIKE 'Load Process%' AND r.benchmark NOT LIKE 'Has been%';



The issue with this is, Since the date format does not contain AM or PM in it ... while subtracting time values crossing midnight the value goes in negative .... How to subtract two date strings with out AM or PM values to get a positive value ?



Thanks in advance
Arun


Re: Subtracting two dates in string format with out AM or PM [message #596868 is a reply to message #596867] Thu, 26 September 2013 20:02 Go to previous messageGo to next message
BlackSwan
Messages: 22691
Registered: January 2009
Senior Member
ask the genius who "designed" this record how to properly process the data.
This is classic example why date/time should only ever be stored as DATE datatype; otherwise Garbage In, Garbage Out.


How do you differentiate between times before noon and after noon?

[Updated on: Thu, 26 September 2013 20:18]

Report message to a moderator

Re: Subtracting two dates in string format with out AM or PM [message #596871 is a reply to message #596868] Thu, 26 September 2013 22:56 Go to previous messageGo to next message
arunkumarsd
Messages: 40
Registered: June 2009
Location: India
Member
Could not agree more with you. I am literally pulling my hair with this issue .... This is a old system and no proper owner and shell script writes this data into the column. The worst is I have to find a way with this existing table structure rather than re designing( there is too many components to touch for re design ). Please let me know if there is any way in which it can be done... algorithm or just a hint is also fine.


Thank you
Arun
Re: Subtracting two dates in string format with out AM or PM [message #596872 is a reply to message #596871] Thu, 26 September 2013 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 22691
Registered: January 2009
Senior Member
How do you differentiate between times before noon and after noon?
icon5.gif  Re: Subtracting two dates in string format with out AM or PM [message #596878 is a reply to message #596867] Fri, 27 September 2013 00:44 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What should be the result for the sample data you gave?

Previous Topic: Problem with Collection
Next Topic: ORA-29540
Goto Forum:
  


Current Time: Sat Aug 23 03:17:38 CDT 2014

Total time taken to generate the page: 0.44013 seconds