Separate Date and time Feld from Query on Control-M

Tools and several solutions to manage Control-M products
Post Reply
User avatar
osafr71
Nouveau
Nouveau
Posts: 26
Joined: 21 Jan 2010 12:00

Separate Date and time Feld from Query on Control-M

Post by osafr71 » 03 Dec 2012 4:25

Hi there, i have an issue regarding Reporting os Sucess Of Jobs ended OK.
I run this query against a View of ours called CUST_ALLJOBS.

Select JOB_NAME,MEMNAME,ODATE, elapsed, start_time, end_time
FROM CUST_ALLJOBS where STATUS like 'Ended OK' and GROUP_NAME like 'DW-SCADA' and ODATE like '1211%'
and JOB_NAME like 'DIARIO_NEW'

The result is like this:

DIARIO_NEW Diario.bat 121105 20121106035931 20121106060638

But our costumer needs to have separated the date and time in 2 columns. Anyone have done this? Or exist some other Table where i can have the same results but with Date and Time separated.

Regards,

osafr71

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 03 Dec 2012 6:11

For this kind of report why you dont use the Reporting Facility ?
:D

But you are true: the problem of the start time/end time format remain. You can export the report on xls files and in excel filtering the column values showing only time ?

Regards

User avatar
osafr71
Nouveau
Nouveau
Posts: 26
Joined: 21 Jan 2010 12:00

Post by osafr71 » 03 Dec 2012 6:18

Hello. You are correct, but my problem is our Service Manager doesn't won't to "spent any time " formatting Excel.

The Reporting Facility i have tested some solutions, but how cai i pass de Last Month option?
This report run's as first day of each month and the result must be the data of previous month. It's possible to pass %%CALCDATE %%MONTH -1 ?

osafr71

User avatar
mauriziog
Nouveau
Nouveau
Posts: 807
Joined: 08 Jun 2007 12:00
Location: Varese - Italy
Contact:

Post by mauriziog » 03 Dec 2012 6:28

For the date, if you use the active jobs report you can create a template that extract not the current AJF but the previous day. Then you schedule the report at the first day of the month.

For dates formats, you can define a macro in the excel sheet, so that can be executed when the sheet is opened: no work for the users....
:D

User avatar
zailaiboke
Nouveau
Nouveau
Posts: 3
Joined: 01 Aug 2013 12:00

Post by zailaiboke » 01 Aug 2013 11:44

For the date, if you use the active jobs report you can create a template that extract not the current AJF but the previous day. Then you schedule the report at the first day of the month.

For dates formats, you can define a macro in the excel sheet, so that can be executed when the sheet is opened: no work for the users....
Very Happy




________________________________________________
FIFA Ultimate Coins

User avatar
anirudhrh
Nouveau
Nouveau
Posts: 48
Joined: 21 Sep 2013 12:00

Post by anirudhrh » 28 Jan 2014 8:09

this myt help u in the query...

to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
, to_char(to_date(trim(timestmp), 'YYYYMMDDHH24MISS')
, 'YYYY-MM-DD HH24:MI:SS') as end_time
Anirudh

Post Reply