Focal Point
BOTSCHED -- NextRunTime
October 19, 2006, 03:17 PM
Michael SimonBOTSCHED -- NextRunTime
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
October 19, 2006, 03:31 PM
<Tim Howard_ABCBS>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.htmlWe're looking at doing something similar.
October 19, 2006, 03:52 PM
Michael SimonThis 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
October 19, 2006, 04:12 PM
Francis MarianiIf 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
WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL
October 20, 2006, 07:28 AM
Tony AMichael,
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 | |
October 20, 2006, 07:36 AM
Tony AOh yes, you'll need my TimeZone include member for the &Offset variable!
-* 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 | |
October 20, 2006, 11:19 AM
jmdoyl1The 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.htmland
http://www.psoug.org/reference/timestamp.htmlIt has sections on extracting just the date using TO_CHAR and date and time using the EXTRACT.
Jon
October 20, 2006, 09:58 PM
susannahMichael, 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 |
October 26, 2006, 06:03 PM
Michael SimonOK... 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