SQL CAST Function

CAST may be used to convert data types.

Casting a Date

The snippett illustrate using a cast in a DECLARE statement. Set cursor for a file where the transaction must fall within the from and to date on file. Substring and concatenate the transaction date to cast as a date type field for the compare. 

      //*----------------------------------------------------------------------
      //* SetPaymentCursor - this procedure creates a dynamic cursor for the use
      //*    in fetching the payment records for the loan statement.
      //*
      //* Parameter       ATR      I/O       Description
      //* ----------      ---      ---       -----------
      //* error indicator    n       O       Error indicator
      //*
      //*----------------------------------------------------------------------

     p SetPaymentCursor...
     p                 b                   export
     d SetPaymentCursor...
     d                 pi              n
     d isFound         s               n   inz(*off)
     d errorOccurred   s               n   inz(*off)
     d qt              s              1a   inz('''')
     d QRYstring       s           1024a
     d len             s              3S 0
      /free

         //*---------------------------------
         //* Substring and concatenate the
         //* transaction date to cast as a
         //* date type field for the compare.
         //*---------------------------------

         QRYstring = 'Select * From VayPay Where vaLoan = ? +
            and vtxfer = ?  and vacode <> ''cnv'' and +
            CAST( concat(substr(DIGITS(VATRDTE),1,4),concat(''-'', +
            concat(substr(DIGITS(VATRDTE),5,2),concat(''-'', +
            substr(DIGITS(VATRDTE),7,2))))) AS DATE) +
             >= ? and +
            CAST( concat(substr(DIGITS(VATRDTE),1,4),concat(''-'', +
            concat(substr(DIGITS(VATRDTE),5,2),concat(''-'', +
            substr(DIGITS(VATRDTE),7,2))))) AS DATE) +
             <= ? ';

         EXEC SQL PREPARE selectString FROM :QRYstring;

         EXEC SQL DECLARE payment4048Cursor
                  SCROLL CURSOR FOR selectString;

         IF SQLSTT = SQLSTTOK       ;
            ErrorOccurred = *off    ;
         ELSE                       ;
            ErrorOccurred = *ON     ;
         ENDIF                      ;

         RETURN ErrorOccurred       ;

      /end-free
     p SetPaymentCursor...
     p                 e

The example below demonstrates multiple uses of CAST. The column DOCLINENUM is being cast as a nine digit decimal. The date column is being cast as an eight digit numeric value. And the VALUEHOME column, which was defined on the table as having only two decimal positions is cast as having three decimal positions. 

          Exec SQL
            Declare WR1010cursor Cursor For
             Select
              Substr (doccode,1,4) as Dtype,
              subStr (docNum,5,8)  as docNo,
              cast(doclinenum as decimal(9,0)) as ieRef,
              substr(ref3,1,20) as dxref3,
              substr (el2,1,12) as dacct1,
              substr (el3,1,12) as dacct2,
              substr (el4,1,12) as dacct3,
              substr (el5,1,12) as dacct4,
              case when(statpay = 84) then 'A'
                   when(statpay = 87) then 'H'
                   when(statpay = 89) then 'P'
                   when(statpay = 93) then 'R'
                   when(statpay = 171) then 'X'
                   Else ' '
              End as DDstat,
              substr(DDescr,1,30) as Dddesc,
              yr,
              period,
              substr(ref2,1,10) as Dxref2,
              ifNull(Cast(Replace(Char(DOCDATE, ISO), '-', '')
                 as  dec(8, 0)),'0') as iVdat,
              substr(ref1,1,20),
             Cast(valueHome as decimal(18,3)) as ddValu
            From OAS_DOCV1
            Where  el2       = :thisAccount
               and yr        = :thisyear
               and period    = :thisPeriod
               and cmpCode   = :thisCompany
              Order by docCode, DocNum ;