7 Date Conversion

Date Conversion

lf a data item has a control number which specifies it to be a date, QUERY uses an algorithm to convert a date format (MM/DD/YY) into an integer for storage. The inverse algorithm is used when the value is output. These algorithms are used to save storage space in the data base and to allow sorting by date. The algorithms are listed below.

! This function converts a date string of the form mm/dd/yy, m/dd/yy,
! mm/d/yy, or m/d/yy to an integer value.  The date must be from
! 1/1/72 to 12/31/99 inclusive.  The integer value returned will be
! in the range 1 to 10227.  If an invalid date string is input,
! the return integer value will be 0.
!
! variables:
!  D      - the day value
!  Dt$    - the date string
!  Dval   - the numeric value of the date
!  I      - a loop index
!  M      - the month value
!  Y      - the year value
!
       DEF FNDate(Dt$)
       INTEGER D,Dval,I,M,Y
!
! Check for proper date string length.
          IF (LEN(Dt$)<=8) AND (LEN(Dt$)=6) THEN Cont1
          Dval=0
          GOTO End
! 
! Insert leading zeros in month and day, if necessary, 
! to get mm/dd/yy.
Cont1:    IF LEN(Dt$)=6 THEN Dt$="O"&Dt$
          IF (LEN(Dt$)=7) AND (Dt$[2,2]="/") THEN Dt$="O"&Dt$
          IF LEN(Dt$)=7 THEN Dt$=Dt$[1,3]&"O"&Dt$[4]
! 
! Check for slashes between month, day and year.
          IF (Dt$[3,3]="/") AND (Dt$[6,6]="/") THEN Cont2
          Dval=0
          GOTO End
! 
! Check for only digits In month,day and year.
Cont2:    FOR I=1 TO 8
             IF (I=3) OR (I=6) THEN Cont3
             IF POS("1234567890",Dt$[I,I])>0 THEN Cont3
             Dval=0
             GOTO End
!
Cont3:    NEXT I
!
! Convert month, ciay and year to numeric.
          M=VAL(Dt$[1,2])
          D=VAL(Dt$[4,5])
          Y=VAL(Dt$[7,8])+1900
!
! Compute the numeric value of the date. 
          Dval=INT(365.2500*(Y-1972)+0.75)+INT(30.550*M-29.950)
-2*(M>2)+D+((M>2) AND (Y/4=INT(Y/4)))
!
! Convert the numeric value of the date back to a string
! to check for valid date.
Cont4:    IF Dt$<>FNDate$(Dval) THEN Dval=0
End:      RETURN Dva1
       FNEND
!
!
! This function converts a date integer vlaue in the range 1 to 10227 
! to a date string of the form mm/dd/yy.  If an invalid date integer
! value is input, the date string will be set to null.
!
! variables:
!  D     - the day value
!  Dt$   - the date string
!  Dval  - the numeric value of the date
!  M     - the month value
!  N     - an intermediate value
!  Y     - the year value
!
       DEF FNDate$(INTEGER Dval)
          INTEGER D,M,N,Y
          DIM Dt$[8]
!
! Check the numeric value for valid range.
          IF (Dval>0) AND (Dval<10228) THEN Cont1
          Dt$=" "
          GOTO End
!
! Convert numeric value of the date to month, day and year.
Cont1:    Y=INT((Dval-L)/365.2500)+1972
          N=Dval-INT(365.2500-(Y-1972)+0.75)
          M=INT((N+31)/30)
          IF INT(30.550*M-29.950)-2*(M>2) AND ((Y/4=INT(Y/4))>N) THEN
 M=M-1
          D=N-INT(30.550*M-29.950)+2*(M>2)-((M>2) AND (Y/4=INT(Y/4)))
! 
! Assemble the date string.
!       Dt$=VAL$(M)&"/"&VAL$(D)&"/"&VAL$(Y-1900)
! 
! Insert leading zeros in ..nth and day, if "ecessary,
! to get mm/dd/yy.
          IF LEN(Dt$)=6 THEN Dt$="0"&Dt$
          IF (LEN(Dt$)=7) AND (Dt$[2,2]="/") THEN Dt$="0"&Dt$
          IF LEN(Dt$)=7 THEN Dt$=Dt$[1,3]&"0"&Dt$[4]
          RETURN Dt$
       FNEND
Eloquence Query Manual - 19 DEC 2002