SQL Numeric to Date during Select 1

[rstitzel]

I use lansa to create my fields and files. Lansa does NOT support date fields (you have to create virtual fields that only work within lansa)

I have a file with a 6 digit numeric field that I store a MDY formatted Delivery Date. I want to be able to make a selection from this file using this field based on a MDY Date Range. In RPG using %date works great but I want to do it during a SQL statement.

I tried the following with no luck:

C/exec sql
C+ Declare INDIV Cursor for
C+ Select OICUST, OIIYWD, OIINVN, OIBLCK, OITYPE, OIBLNG, OIIMDY,OIIAMT, OIPAMT, OIPYWD, OIPMDY, OIEMPN, OIEMSN, OIBTCH, OICSSN, OIPAID, OIUBCH, OICOMM, OIRTE, OIGPID, OIRECN, OIRAMT, OIPONO
C+ From WHOINV
C+ Where CAST(OIIMDY as Date) >= :IStartDate
C+ and Cast(OIIMDY as Date) <=:IEndDate
C+ and OICUST = :ICustNum and OITYPE <=2
C+ Order By OIIMDY
C/end-exec

When compiled I get the error CAST FROM NUMERIC TO DATE NOT SUPPORTED. Does anyone how to convert a numeric to a date during the select statement? Otherwise I'm having to select ALL records and then convert the field to a date field (using %date)and then compare them to my date range.

Thank in advance for any and all help.

 

[Athena32]

I have never tried this so I don't know if it will work but it appears that you can use a Date statement in the select statement. I got this off of code400.com

    SQLRPGLE  - Some date processing with SQL 
SQL Date Support 


This query produces these results:

Order No.  Ship Date
     1     1997-06-20
     2     1997-09-07
     3     1997-08-24
     4     0000-00-00
     5     1997-09-27
     6     0000-00-00
     7     1997-09-29
     8     0000-00-00
     9     1997-09-03
    10     0000-00-00


  SQL has strong support for native date, time, and timestamp data types. 
  To see how to take advantage of SQL’s date support, 
  let’s start with another numeric column in the OrdHdr table called 
  OhDtOr (order date), which is stored in the same packed 8,0 format 
  as OhShDt (YYYYMMDD). To calculate an estimated ship date, 
  we use the Date function to convert the numeric order dates 
  to native, or true, dates and then use SQL’s date support to 
  add 10 days to the order dates (SQL lets us add durations 
  only to date fields, not to numeric or character values):
 

Select OhNum,
   Substr( Digits( OhDtOr ), 1, 4 )|| '-' || 
   Substr( Digits( OhDtOr ), 5, 2 )|| '-' || 
   Substr( Digits( OhDtOr ), 7, 2 ) 
   As OrderDate, 
   Date( Substr( Digits( OhDtOr ), 1, 4 ) 
    || '-' || 
      Substr( Digits( OhDtOr ), 5, 2 ) 
       || '-' || 
      Substr( Digits( OhDtOr ), 7, 2 ) ) 
   + 10 Days As EstShipDate 
  From OrdHdr



This query produces these results:



Order No.  Order Date   EstShipDa
     1     1997-06-15   06/25/97
     2     1997-09-01   09/11/97
     3     1997-08-15   08/25/97
     4     1997-09-29   10/09/97
     5     1997-09-20   09/30/97
     6     1997-09-28   10/08/97
     7     1997-09-18   09/28/97
     8     1997-09-25   10/05/97
     9     1997-08-24   09/03/97
    10     1997-09-27   10/07/97


The third entry in the Select clause uses the Date 
function to return a native date rather than a 
character string. In this case, the Date function 
has a complex character string expression as its argument; 
however, it can take several other types of arguments. 
When the Date function’s argument is a character string, 
the string must be in one of several valid date formats. 
Because the ISO format used in the query is one of the valid 
formats, the Date function returns a native date representing 
the order date.  Notice how I added 10 days to the native 
date by specifying a value of 10 and a duration of Days. 
You can add other values. For example, to calculate a purge 
date, I can add 1 year, 2 months, and 5 days to the order date 
as follows:


Select
  OhNum,
  Date('
   Substr( Digits( OhDtOr ), 1, 4 ) || '-'|| 
   Substr( Digits( OhDtOr ), 5, 2 ) || '-'|| 
   Substr( Digits( OhDtOr ), 7, 2 ) ) 
  + 1 Year + 2 Months + 5 Days 
    As PurgeDate 
  From OrdHdr

 

[rstitzel]

Athena32 you are a GODDESS!

Thanks I got it working!! This is what I came up with:

C/exec sql
C+ Declare INDIV Cursor for
C+ Select OICUST, OIIYWD, OIINVN, OIBLCK, OITYPE, OIBLNG, OIIMDY,OIIAMT, OIPAMT, OIPYWD, OIPMDY, OIEMPN, OIEMSN, OIBTCH,OICSSN, OIPAID, OIUBCH, OICOMM, OIRTE, OIGPID, OIRECN,
C+ OIRAMT, OIPONO
C+ From WHOINV
C+ Where date('20' || substr(digits(OIIMDY),5,2) || '-' ||
C+ substr(digits(OIIMDY),1,2) || '-' ||
C+ substr(digits(OIIMDY),3,2)) between :StartDateIso
C+ and :EndDateISO and OICUST = :ICustNum and OITYPE <=2
C+ Order By OIIMDY
C/end-exec

THANKS FOR YOU HELP AND HAVE A STAR!

 

[Athena32]

rstitzel

I am glad I could help. Thank you for my first star.