Converting Dates
Last Post 05 Dec 2012 08:33 AM by Al Slezak. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Al Slezak
Advanced Member
Advanced Member
Posts:571
Avatar

--
04 Dec 2012 01:48 PM

I need to find an SQL funcion that will convert from a date format to a CYMD format.  All the dates on our system are in the format CYMD and designed that way on the database.  Don't ask me why it is just that way.  Anyway we have some thrid part interface that gives us GUI screens for our iSeries data.  I need to add 14 days to a date then convert it back to a CYMD format so that it will be recognized by the software.   There is a function that I have used called CYMDTODATE that converts CYMD for to date format.  I do not know where this comes from but I need to convert it the other way from a DATE format to a CYMD format.  Is there a function available out there that does this or how would you go about creating one.  I run this in interactive SQL and it appears fine.  The problem is that when I use this GUI interface I need to select the format of one of the fields on the data base.  Unfortunely we only have this CYMD format for dates.

Can any one help me with this?

 

Al

Al Slezak
Advanced Member
Advanced Member
Posts:571
Avatar

--
04 Dec 2012 04:08 PM

I have been working on creating a function that I can use my self to pass in a date in CYMD format and then pass back a CYMD format.  Here is the function

Monitor;                               
    DateField = %date(piCrtdt:*CYMD);    
    Datefield += %days(piDays);          
    wAddDays = %dec(Datefield:*cymd);    
  On-Error;                              
    wAddDays = *Zeros;                   
  EndMon;

This is what I pass in

*************** Beginning of data *****************************************************************************
 D fcAddDays       PR             7S 0                                                               121204    
 D  piCrtDt                       7S 0 Const                                                         121204    
 D  piDays                        9S 0 Const                                                         121204

This is the function I created

create function fc/fcadddays (      
CRTDT Decimal (7,0),                
casno Numeric(9,0) )                
Returns Decimal(7,0)                
Language RPGLE                      
SPECIFIC FC/FCADDDAYS               
DETERMINISTIC                       
NO SQL                              
CALLED ON NULL INPUT                
NO EXTERNAL ACTION                  
EXTERNAL NAME 'FC/FC068S(FCADDDAYS)'
PARAMETER STYLE GENERAL

I do it interactively with this command

FCADDDAYS(@FCTICK.CMP03,14) as EXPRIC,

I get a data decimal error.  What am I doing incorrectly

Al

 

 

 

Jim_IT
Basic Member
Basic Member
Posts:217

--
04 Dec 2012 09:19 PM

Al,

A user defined function is really not needed to convert date field to CYYMMDD.  This mash up of native scalar functions will do it.

Decimal(Replace(Char(Current Date, iso), '-', '')-19000000,7,0)

 

Jim

Al Slezak
Advanced Member
Advanced Member
Posts:571
Avatar

--
05 Dec 2012 08:33 AM

Jim,

Thanks that actually worked.  I guess I will have to read up about the native scalar functions.

Al

You are not authorized to post a reply.

Acceptable Use Policy