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 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.

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.

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

Michael:

Try this site - it might help:

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

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

Oh 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

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

Michael, give this a read
foouc on developers article re the botsched file

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.