Elapsed Time returned from query

[Bob]

I am using excel to query DB2. The field in DB2 Elapsed_Time has been
calculated from a End_Date_Time Start Date Time. The Problem is that excel
treats this value 00:00:00 as mm/dd/yyyy 00:00:00 AM/PM and there is nothing
I can do to change the format once it is in Excel. Right now I have reverted
to querying the Start/End Date Time and performing the calculation in excel
and formating as an elapsed time. I am using the Microsoft Query Wizard from
excel.

Unfortunately that will not work for some of the data as CPU Elapsed Time is
calculated and there is no CPU Start/Stop Date Time values it has been
summarized in the database. I am totally stumped

We have a query gateway (Web tool) and if I use that to query DB2 and export
to excel it works fine (elapsed time can be formatted as elapsed time). So
the issue seems to be in the query return from query manager but I cannot
find any way to format using the query manager application.

Any help would be awesome.

 

[Greg Lovern]

Hi Bob,

-- While in MsQuery, double-click the affected field header. The Edit
Column dialog should appear.
-- Concatenate a custom string before the field name. The syntax,
depending on DB2, is probably:
<apostrophe><your string><apostrophe><plus sign><field name>
for example:
'DeleteMe'+Elapsed_Time
Important: It should NOT start with an equals sign.
-- Return that to Excel. Excel will treat it as a string.
-- In another column, parse out the custom string with RIGHT, wrap it
in VAL, and apply the number formatting you want.


Another approach:

Depending on DB2, it might recognize functions similar to Excel's such
as Hour, Minute, Second (the DB2 documentation should say, or you
could ask your DB2 admin). In MS Query's Edit Column dialog you could
try something like MINUTE(Elapsed_Time) or maybe it's MINUTE
[Elapsed_Time]. Then repeat as needed for hour and second. Then, in
Excel, use the TIME function to bring them together, and then number
format as needed.



Good luck,

Greg Lovern
http://PrecisionCalc.com