Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     BOTSCHED -- NextRunTime

Read-Only Read-Only Topic
Go
Search
Notify
Tools
BOTSCHED -- NextRunTime
 Login/Join
 
Member
posted
I've searched the site and this board nothing answers the questions quite right.

I'm looking for a way via SQL not FOCUS code, to convert the nextruntime into a date time value.

Can anyone help?

Thanks
ver 8.1.5 server 8.1.4 Client


Have used WF 5.x, 7.x w/RC, BID, MR
WAS 5.x/6.x
AIX 5.3.0
 
Posts: 29 | Location: Wine Country | Registered: October 10, 2006Report This Post
<Tim Howard_ABCBS>
posted
Have you tried executing a stored procedure?

Here's a good article on what you're trying to do:

http://www.informationbuilders.com/support/developers/rcaster.html

We're looking at doing something similar.
 
Report This Post
Member
posted Hide Post
This is an artical I looked at. First of all we don't use SQL Server. We use Oracle. 2nd stored procedure from what I can make out of it adds 10 minutes to the time. It doesn't convert it to a date time.

And then the rest of the story is focus code.

Not much help.


Have used WF 5.x, 7.x w/RC, BID, MR
WAS 5.x/6.x
AIX 5.3.0
 
Posts: 29 | Location: Wine Country | Registered: October 10, 2006Report This Post
Expert
posted Hide Post
If you can figure out the Date and Time from the NextRunTime field and you should be able to, since you know what the contents of the field is - IE, the number of milliseconds since January 1, 1970 (incidentally, what this has to do with Zulu Time, I can't figure out - why on earth wasn't this field a Date/Time field?) then you use the SQL function CONVERT to convert it to a Date/Time value.

CONVERT(smalldatetime, 'value')

This might be of some use:

http://www.karaszi.com/SQLServer/info_datetime.asp


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Michael:

Try this site - it might help:

http://www.ibm.com/developerworks/db2/library/techartic...211yip/0211yip3.html


WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL
 
Posts: 77 | Location: Baltimore | Registered: May 31, 2006Report This Post
Expert
posted Hide Post
Michael,

I had already achieved this as a result of a prototyping proc that I needed. I took the detail from Jodye Yate's article (mentioned above by Tim) and added this code which utilises a WF Function -

-SET &Time = EDIT(&TOD,'99$99');
-INCLUDE TimeZone
-SET &OFFSETHOURS =-(&Offset/60);
 
DEFINE FUNCTION ZULUTIME(OFFSET/A10, VALUE/D4)
NOWDATETIME_A/HYYMDS=HGETC(8, 'HYYMDS');
NOWDATETIME/HYYMDS=HADD(NOWDATETIME_A, OFFSET, VALUE, 8, 'HYYMDS');
DAYSDIFF/D8=DATEDIF('19700101', '&YYMD', 'D');
MSDIFF/D32=DAYSDIFF*24*60*60*1000;
ADDOFFSET/D12=&OFFSETHOURS*60*60*1000;
NOWMILLISEC/D12 = HTIME(8, NOWDATETIME, 'D12.2');
NEEDTOADD/D13=NOWMILLISEC+ADDOFFSET+MSDIFF;
ZULUTIME/A17=FTOA(NEEDTOADD, '(D13c)', 'A17');
END
-RUN
 
-SET &NextRunTime='0000000000000000000' | ZULUTIME('minute',-110);
 
SQL
  UPDATE BOTSCHED
     SET DISTLIST = '[put your detail here if you need to change it]'
       , STARTDATE = '&YYMD'
       , STARTTIME = '&Time'
       , UPDATEDATE = '&YYMD'
       , UPDATETIME = '&Time'
       , NEXTRUNTIME = '&NextRunTime'
   WHERE SCHEDULEID = '[set this to the value of your schedule id]'
;
TABLE FILE SQLOUT
PRINT *
END
-RUN


You might have to play with the offset that you use (I used -110) but it does work OK.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Oh yes, you'll need my TimeZone include member for the &Offset variable! Wink
-* TimeZone.fex
-*SET &ECHO=ALL;
-* system call to write the registry value to a file
! REG QUERY HKLM\SYSTEM\CURRENTCONTROLSET\CONTROL\TIMEZONEINFORMATION /V ACTIVETIMEBIAS > TZ.TXT
-*(above must be on one line)
-* filedef the file generated so that it can be read using a -READ
APP FI TZ DISK TZ.MAS
-RUN
-WRITE TZ
-WRITE TZ FILE=TZ,SUFFIX=FIX
-WRITE TZ SEGNAME=SEG1
-WRITE TZ FIELD=DATA_KEY,,A80,A80,$
-WRITE TZ DEFINE HEX/A10 = RJUST(10,GETTOK(DATA_KEY, 128, -1, 'x', 10, 'A10'),'A10');
-WRITE TZ DEFINE HEX1A/A3 = EDIT(HEX,'$$$$$$$9$$');
-WRITE TZ DEFINE HEX2A/A3 = EDIT(HEX,'$$$$$$$$9$');
-WRITE TZ DEFINE HEX3A/A3 = EDIT(HEX,'$$$$$$$$$9');
-* Put each of the following -WRITEs on one line
-WRITE TZ DEFINE HEX1/I3 = IF HEX1A EQ ' ' THEN 0
 ELSE IF HEX1A FROM '0' TO '9' THEN BYTVAL(HEX1A, 'I3') - 48
 ELSE BYTVAL(HEX1A, 'I3') - 87 ;
-WRITE TZ DEFINE HEX2/I3 = IF HEX2A EQ ' ' THEN 0
 ELSE IF HEX2A FROM '0' TO '9' THEN BYTVAL(HEX2A, 'I3') - 48
 ELSE BYTVAL(HEX2A, 'I3') - 87 ;
-WRITE TZ DEFINE HEX3/I3 = IF HEX3A EQ ' ' THEN 0
 ELSE IF HEX3A FROM '0' TO '9' THEN BYTVAL(HEX3A, 'I3') - 48
 ELSE BYTVAL(HEX3A, 'I3') - 87 ;
-RUN
FILEDEF TZ DISK ./TZ.TXT (LRECL 80 RECFM V
-RUN
 
TABLE FILE TZ
PRINT
  COMPUTE MyOffset/D5 = ( -4096 * (HEX CONTAINS 'fffff')) + ((HEX1 * 256) + (HEX2 * 16) + HEX3);
WHERE DATA_KEY CONTAINS 'ACTIVETIMEBIAS'
ON TABLE SAVE AS HEXVALUE
END
-RUN
-* read the saved data that contains the required value
-READ HEXVALUE &Offset.A5.
-RUN

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
The example I have to extract Date and Time is for DB2 but since your are using Oracle. Check out this site:

http://www.psoug.org/reference/date_func.html
and
http://www.psoug.org/reference/timestamp.html

It has sections on extracting just the date using TO_CHAR and date and time using the EXTRACT.

Jon
 
Posts: 39 | Registered: January 26, 2004Report This Post
Expert
posted Hide Post
Michael, give this a read
foouc on developers article re the botsched file




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Member
posted Hide Post
OK... I've figured it out and I thought I'd pass it along. Ends up another tool of ours that I did some reports for uses this same type of approach. The formula ends up being quite easy once you know what it is.

So here it is...
TO_DATE('01/01/1970','MM/DD/YYYY')+((((to_number(a.nextruntime))/1000)/(60*60*24))-8/24) NextRunDate

Take the nbr milliseconds and divide by 1000
divide (min * sec * hours). Then based upon your GMT Offset subtract that/24.

The above (8/24) is what PST will be in 2-3 days.

Hope this helps others.


Have used WF 5.x, 7.x w/RC, BID, MR
WAS 5.x/6.x
AIX 5.3.0
 
Posts: 29 | Location: Wine Country | Registered: October 10, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     BOTSCHED -- NextRunTime

Copyright © 1996-2020 Information Builders