I am replicating a Crystal 8.5 report in FOCUS 7.1. The time field I am trying to include in calculations is stored as a Number field in Crystal (a Packed in FOCUS).
(I.E., 11:25 is 1173972349 in output).

In reviewing the Crystal field calculation the following Crystal function was used to display the time as 11:25 :

Mid( UnixTimeToString1 ({casemas.SH_TIMESCHED}), InStr( UnixTimeToString1 ({casemas.SH_TIMESCHED}), " ") + 1, 8)

Is there a comparable function in FOCUS so that I may convert this number to a time value?

I don't think there is a function as such, but you can work it out manually.

The Unix date is number of seconds from a base of 00:00:00 31/12/1969. One day is approx 86400 secs.
So take number MOD 86400. The result will be the number of seconds into a day. Take the reult and divide by sixty. Take the integer of the final result/60 to get the hour and then final result MOD 60 to get the minutes. Something like this:

  
TIME/D12=1173972349;
R1/I8= IMOD(TIME,86400,R1);
HOUR/I2 = (R1/60)/60;
MIN/I2 = IMOD((R1/60),60,MIN);
ALPHA_TIME/A5 = EDIT(HOUR) | ':' | EDIT(MIN);

Thanks, I will try this and respond if it works.

Alan instead of getting 11:45 I am getting 15:45 can you help. Don't know where to tweak to make it be 11:45.

Nailzz

This is UTC time, so you have to convert UTC to your local time, at the moment this means you have to subtract 14400, 4 hours * 60 * 60. But of course this varies for daylight saving. So you have to pick up your UTC offset from the server. I would use a bit of JS, but not sure if you can in your circumstance.

Thanks, didn't even think about that. I will try this.

Alan is right, you must account for time zone differences. However, the number of hours to subtract changes throughout the year due to DST. I wrote a DEFINE FUNCTION that you can use to determine if a date is during DST. If it is then subtract 4 hours else subtract 5 hours.

Input to the function is a smart date. Output is an A1 with a 'Y' or an 'N'.

I hope this is useful.

-***************************************
-* The following function is used to
-* determine whether or not a certain
-* date is during day light savings time
-* or not.
-* RETURN FORMAT: A1 (Y or N)
-***************************************
DEFINE FUNCTION DSTIMEYN (CURNT_DT/YYMD)
CURNT_YR/YY=CURNT_DT;
CURNT_MT/M =CURNT_DT;
CURNT_DOW/W=CURNT_DT;
CURNT_DOM/D=CURNT_DT;
CURNT_I_DOW/I1=CURNT_DOW;
CURNT_I_DOM/I2=CURNT_DOM;
-***************************************
MTH_ID/A4 =
IF (CURNT_YR LE '2006') THEN
DECODE CURNT_MT( 4 '0604' 10 '0610' ELSE '????') ELSE
DECODE CURNT_MT( 3 '0703' 11 '0711' ELSE '????');
-***************************************
-* The following are used for 2006 and prior.
-***************************************
APR_ID/A1 =
IF (CURNT_I_DOM GE 8) THEN 'Y' ELSE
IF (CURNT_I_DOW EQ 7) THEN 'Y' ELSE
IF (CURNT_I_DOW EQ CURNT_I_DOM) THEN 'N' ELSE
IF (CURNT_I_DOW GT CURNT_I_DOM) THEN 'N' ELSE 'Y';
OCT_ID/A1 =
IF (CURNT_I_DOM LE 24) THEN 'Y' ELSE
IF (CURNT_I_DOW EQ 7) THEN 'N' ELSE
IF (CURNT_I_DOW EQ (CURNT_I_DOM - 24)) THEN 'Y' ELSE
IF (CURNT_I_DOW GT (CURNT_I_DOM - 24)) THEN 'Y' ELSE 'N';
-***************************************
-* The following are used for 2007 and beyond.
-***************************************
MAR_ID/A1 =
IF (CURNT_I_DOM LE 7) THEN 'N' ELSE
IF (CURNT_I_DOM GE 15) THEN 'Y' ELSE
IF (CURNT_I_DOW EQ 7) THEN 'Y' ELSE
IF (CURNT_I_DOW EQ (CURNT_I_DOM - 7)) THEN 'N' ELSE
IF (CURNT_I_DOW GT (CURNT_I_DOM - 7)) THEN 'N' ELSE 'Y';
NOV_ID/A1 =
IF (CURNT_I_DOM GE 8) THEN 'N' ELSE
IF (CURNT_I_DOW EQ 7) THEN 'N' ELSE
IF (CURNT_I_DOW EQ CURNT_I_DOM) THEN 'Y' ELSE
IF (CURNT_I_DOW GT CURNT_I_DOM) THEN 'Y' ELSE 'N';
-***************************************
DSTIMEYN/A1=
IF (CURNT_YR LE '2006') AND (MTH_ID EQ '????') THEN
DECODE CURNT_MT(1 N 2 N 3 N 4 ? 5 Y 6 Y 7 Y 8 Y 9 Y 10 ? 11 N 12 N) ELSE
IF (CURNT_YR GT '2006') AND (MTH_ID EQ '????') THEN
DECODE CURNT_MT(1 N 2 N 3 ? 4 Y 5 Y 6 Y 7 Y 8 Y 9 Y 10 Y 11 ? 12 N) ELSE
IF (MTH_ID EQ '0604') THEN APR_ID ELSE
IF (MTH_ID EQ '0610') THEN OCT_ID ELSE
IF (MTH_ID EQ '0703') THEN MAR_ID ELSE
IF (MTH_ID EQ '0711') THEN NOV_ID ELSE '@';
END
TABLE FILE EMPDATA
PRINT SEG.PIN
COMPUTE IN_DST/A1=DSTIMEYN(HIREDATE);
ON TABLE SET STYLE *
DEFMACRO=NDST, BACKCOLOR=GREEN, COLOR=WHITE, STYLE=BOLD,
WHEN=IN_DST EQ 'N',$
DEFMACRO=YDST, BACKCOLOR=PURPLE, COLOR=WHITE, STYLE=BOLD,
WHEN=IN_DST EQ 'Y',$
TYPE=DATA, COLUMN=HIREDATE, MACRO=YDST,$
TYPE=DATA, COLUMN=HIREDATE, MACRO=NDST,$
TYPE=DATA, COLUMN=IN_DST , MACRO=YDST,$
TYPE=DATA, COLUMN=IN_DST , MACRO=NDST,$
ENDSTYLE
END

Thanks guys. What Alan sent worked.

Just as a follow on, in case anyone is interested, I found the UTC code I use:

  
.
.
-HTMLFORM BEGIN
<html>
<head>
<script language="JavaScript1.2" type="text/javascript" runat=server>
function currentUTC(){
var d, s;
d = new Date();
s = d.getTimezoneOffset()*60;
return(s);
}
</script>
<script language="JavaScript1.2" type="text/javascript">
function runFunc(){
var t = currentUTC();
document.getElementById("myVar1").value=t;
document.getElementById("runForm").submit();
}
</script>
</head>
<body>

!IBI.FIL.HOLD;

<form method="post" name="runForm" id="runForm" action="WFServlet">
<input type="hidden" name="IBIF_focexec" id="IBIF_focexec" value="myFex">
<input type="hidden" name="myVar1" id="myVar1" value="">
<input type="button" name="myBut" id="myBut" value="submit" onclick="runFunc();">
</form>
</body>
</html>
-HTMLFORM END

or use javascript on a drill down, instead of the button.

This gives the offset in seconds.