Our back end Data base stores everything in GMT. I need to be able to run report where ticket open date is GE to a EST date/time. I have been able to create a computed field that will convert the GMT to EST but that is after the selection is already done so my record selection that is retured is off a bit. Record selection is still based on GMT.

Here is how I computed the date/time for EST but this is after record selection. I would like to do this in the Where clause somehow.

-SET &CURR_DATE = &YYMD;
-SET &START_DST = EDIT(&CURR_DATE, '99990310');
-SET &END_DST = EDIT(&CURR_DATE, '99991103');

DEFINE FILE PROBSUMMARYM1
opentimeconvert/YYMD=HDATE(PROBSUMMARYM1.PROBSUMMARYM1.OPEN_TIME, 'YYMD');
startdst/YYMD=&START_DST;
enddst/YYMD=&END_DST;

COMPUTE opentimedst/HMDYYS = IF PROBSUMMARYM1.PROBSUMMARYM1.opentimeconvert FROM startdst TO enddst THEN HADD(PROBSUMMARYM1.PROBSUMMARYM1.OPEN_TIME, 'HOUR', -4, 8, 'HMDYYS') ELSE HADD(PROBSUMMARYM1.PROBSUMMARYM1.OPEN_TIME, 'HOUR', -5, 8, 'HMDYYS'); AS 'OPEN TIME'

This message has been edited. Last edited by: <Kathryn Henning>,

Instead of performing your query on the offset database field, why not subtract the appropriate # of hours from the filter?

WHERE ORIGINAL DB_DATETIME_FIELD GE &userSuppliedDateTimewithOffsetApplied

Tony

Using Reg.exe to Offset Time Zones From a Windows-Based System

http://www.informationbuilders.../developers/timezone

If you happen to be on a LINUX/UNIX then it's extremely easy

from inside a WebFOCUS focexec run the following code

FILEDEF tz DISK tz.sh (APPEND
FILEDEF ESTDATE DISK date.ftm
-RUN
-WRITE tz bash
-WRITE tz export TZ=EST
-WRITE tz date > date.ftm
-WRITE tz cat date.ftm
!chmod 777 tz.sh
!./tz.sh
-* read the file to get variables
-READ ESTDATE &x.A4. &ESTMON.A3. &x.A1. &ESTDAY.A2. &x.A1. &ESTTIME.A8.

The nice thing about this on UNIX is that it accomodate daylight saving