Calculating minutes out of service using a date time field

[jaymoore1756]

I have two fields 1) creation_date_time and 2) close_date_time. Both fields are formatted as Date Time. The Creation field records the start of an incident (i.e. Feb 23, 2015 4:28:23 PM ) and Close field records the end of the incident (i.e. Feb 24, 2015 12:11:37 PM) . From these fields I need to calculate the minutes that the incident took to resolve. What do I need to do to convert these fields and calculate the time ?  Thanks for the help in advance.

[Robl]

If you just delete one from the other you should get an interval.
Then you can just set the display format for it, or possibly use an extract funtion to get the minutes of the interval as an integer.

[BigChris]

Try this one on for size

http://www.cognoise.com/community/index.php/topic,7912.msg25908.html#msg25908

[jaymoore1756]

I used this ...

if ([CLOSE_DATETIME] is not null ) Then ((extract(hour,[CLOSE_DATETIME])*60+extract(minute,[CLOSE_DATETIME]))-(extract(hour,[CREATION_DATETIME])*60+extract(minute,[CREATION_DATETIME])) +_days_between ([CREATION_DATETIME],[CLOSE_DATETIME])*1440) else ('0')
and got this error ( see attachment ) Any thoughts

[jaymoore1756]

This the error .....
XQE-DAT-0001

      Data source adapter error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
ORA-30076: invalid extract field for extract source
- when processing query: WITH

[BigChris]

Well for a kickoff I wouldn't put the zero in quotes - there's no need for it to be a string and that's probably throwing the rest of the calculation.

[jaymoore1756]

Reset the calculation to .... if ([CLOSE_DATETIME] is not null ) Then ((extract(hour,[CLOSE_DATETIME])*60+extract(minute,[CLOSE_DATETIME]))-(extract(hour,[CREATION_DATETIME])*60+extract(minute,[CREATION_DATETIME])) +_days_between ([CREATION_DATETIME],[CLOSE_DATETIME])*1440) else (0) Ran the validate on the data item and got the error
XQE-DAT-0001

      Data source adapter error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
ORA-30076: invalid extract field for extract source

Framework shows the following .. for the extract sources (CLOSE_DATETIME, CREATION_DATETIME 
data type  -- Date Time
Usage -- Identifier
Size  --- 12
Format is date time with defaults

[jaymoore1756]

I am still trying to get this calculation to work ---

I have reset the calculation to .... if ([CLOSE_DATETIME] is not null ) Then ((extract(hour,[CLOSE_DATETIME])*60+extract(minute,[CLOSE_DATETIME]))-(extract(hour,[CREATION_DATETIME])*60+extract(minute,[CREATION_DATETIME])) +_days_between ([CREATION_DATETIME],[CLOSE_DATETIME])*1440) else (0)
Ran the validate on the data item and got the error
XQE-DAT-0001
Data source adapter error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
ORA-30076: invalid extract field for extract source

Framework shows the following formatting for the extract sources (both are the same)
CLOSE_DATETIME, CREATION_DATETIME 

data type  -- Date Time
Usage -- Identifier
Size  --- 12
Format is date time with defaults
When I run the report I this is an example of the output I get
CREATION_DATETIME   Mar 15, 2015 4:26:15 PM   
CLOSE_DATETIME  Mar 15, 2015 9:21:51 PM
When I start to break the components of the formula down I get the following
Example 1 extract ( Year,[CREATION_DATETIME]) gives me data ie 2015
Example 2 extract ( day,[CREATION_DATETIME]) gives me data ie 7    
Example 3 extract (Hour,[CREATION_DATETIME]) gives me ...

XQE-DAT-0001

Data source adapter error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 11
ORA-30076: invalid extract field for extract source

So it appears that there is an issue in the hour minute part of the calculation . Has anyone seen this ?

[BigChris]

Do you get the same error is you try extract(Hour, getdate()) {or possibly extract(Hour, sysdate())...looks like you're using an Oracle database}?

[jaymoore1756]

Neither of these work ..extract(Hour, getdate()) {or possibly extract(Hour, sysdate())...

[BigChris]

Ok - so it looks like there's something wrong with extract (hour, [SomeDate]) then since extract(hour, getdate()) isn't using any of the data from your database.

[jaymoore1756]

Thanks for trying to help BICHRIS !!! I think there is something wrong with the data element I am able to extract year day but I cannot extract hour minute. Is there a work around ?