Conference ilbbak::ibi_focus

125.0. "Problem with Date Selection from RDB file" by NRPUR::CHABOT (Jerry Chabot) Fri Sep 02 1988 16:05

    I'm having a problem retrieving records from an RDB file based on
    a date selection. I believe I am using the correct format of a date
    by specifying YYYYMMDD. 
    
    In my example I have the following in the RDB file.
    
        Badge No    Sys Date
         TEST01      29-JUL-1988 00:00:00.00
         TEST02      30-JUL-1988 00:00:00.00
         TEST03      31-JUL-1988 00:00:00.00
         TEST04      01-AUG-1988 00:00:00.00

    If I select records with a SYS_DT LT 19880801 I get four records.
    If I select SYS_DT LT 19880731 I get no records!!
    
    I've included the necessary commands that will show the problem that
    I'm having. It will create an RDB file, store the test records and
    run a FEX to create a TESTEX.DOC report with a few test conditions.
    
    We are running VMS 4.7 , RDB 2.3 and FOCUS 5.2
    
    Any help would be greatly appreciated.
    
    
$RDO
SET VERIFY
!
! Define the Rdb Database.
! 
DEFINE DATABASE 'FOCTST.RDB'.
!
! Define the Fields.
!
DEFINE FIELD   BADGE_NO
    DATATYPE IS   TEXT SIZE IS 6.
DEFINE FIELD   SYS_LAST_UPD_DT
    DATATYPE IS   DATE.
!
! Define the Relation.
!
DEFINE RELATION EMPLYE_RLN.
  BADGE_NO.
  SYS_LAST_UPD_DT.
END EMPLYE_RLN RELATION.
!
! Define index
!
DEFINE INDEX EMPLYE_IDX FOR EMPLYE_RLN
     	DUPLICATES ARE NOT ALLOWED.
     	BADGE_NO.
END EMPLYE_IDX INDEX.
!
COMMIT
FINISH
!
! End Database Create.
!
INVOKE DATA FILE 'FOCTST'
START_TRANSACTION READ_WRITE RESERVING EMPLYE_RLN FOR SHARED WRITE
STORE E IN EMPLYE_RLN USING
 E.BADGE_NO = "TEST01";
 E.SYS_LAST_UPD_DT = "29-JUL-1988 00:00:00.00"
END_STORE
STORE E IN EMPLYE_RLN USING
 E.BADGE_NO = "TEST02";
 E.SYS_LAST_UPD_DT = "30-JUL-1988 00:00:00.00"
END_STORE
STORE E IN EMPLYE_RLN USING
 E.BADGE_NO = "TEST03";
 E.SYS_LAST_UPD_DT = "31-JUL-1988 00:00:00.00"
END_STORE
STORE E IN EMPLYE_RLN USING
 E.BADGE_NO = "TEST04";
 E.SYS_LAST_UPD_DT = "01-AUG-1988 00:00:00.00"
END_STORE
COMMIT
EXIT
$FOCUS
FILEDEF FOCTST DISK FOCTST.RDB
TABLE FILE FOCTST
 HEADING 
   "ALL RECORDS"
PRINT BADGE_NO SYS_DT
ON TABLE HOLD AS TESTFOC FORMAT DOC
END
TABLE FILE FOCTST
 HEADING 
   "BEFORE 19890101"
PRINT BADGE_NO SYS_DT
IF SYS_DT LT 19890101
ON TABLE HOLD AS TESTFOC FORMAT DOC
END
TABLE FILE FOCTST
 HEADING 
   "BEFORE 19880801"
PRINT BADGE_NO SYS_DT
IF SYS_DT LT 19880801
ON TABLE HOLD AS TESTFOC FORMAT DOC
END
TABLE FILE FOCTST
 HEADING 
   "BEFORE 19880731"
PRINT BADGE_NO SYS_DT
IF SYS_DT LT 19880731
ON TABLE HOLD AS TESTFOC FORMAT DOC
END
TABLE FILE FOCTST
 HEADING 
   "BEFORE 19880730"
PRINT BADGE_NO SYS_DT
ON TABLE HOLD AS TESTFOC FORMAT DOC
IF SYS_DT LT 19880730
END
FIN

    Here is the FOCTST.MAS and FOCTST.ACX files for the example given
    in 125.0.
        
FILENAME=FOCTST, SUFFIX=RDB
SEGNAME=EMPLYE, SEGTYPE=S0 
 FIELDNAME=BADGE_NO        ,ALIAS=            ,USAGE=A6      ,ACTUAL=A6     ,$
 FIELDNAME=SYS_DT          ,ALIAS=            ,USAGE=I8YYMTD ,ACTUAL=A8     ,$

    
SEGNAME=EMPLYE, TABLENAME=EMPLYE_RLN      ,$ 
 FIELD=BADGE_NO    ,ALIAS=BADGE_NO        ,$
 FIELD=SYS_DT      ,ALIAS=SYS_LAST_UPD_DT ,$
        

    
    My original note should have said when SYS_DT LT 19880801 that three
    records were found.
    

	Try this :

 FIELDNAME=SYS_DT          ,ALIAS=            ,USAGE=A8 ,ACTUAL=A8     ,$

	and : IF SYS_DT LT '19880801'        

	If you define USAGE=A16 you can also test the time 
	'1988080122090000' for 1st Aug 1988 10:09PM

			- Jo�l -