ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Create Function ... HELP

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create Function ... HELP

    OK Ladies and Germs...

    What gives here...? I'm trying to create a "SIMPLE" little function and it's crapping out on me.

    What I'd like to do is this ... I have a table that has Date/Time stores in "8s0" format (YYYYMMDD/HHMMSS00 respectively)

    I'd like to do a select statement with something like this...

    PHP Code:
    Select AcctNoFmtDateIss_DT ), FmtTimeIss_TM ), CurrBal From TableName 
    Where the FmtDate Function takes '20060820' and converts it to '2006-08-20' and FmtTime takes '13223900' and converts it to '13:22:39'.

    I created the following function (to start with) and tried the above call... it doesn't work.

    PHP Code:
    Create Function FmtDate(In_Date Numeric)                
    Returns Date                                            
    Language SQL                                            
    Return DateSubStrDigitsIn_Date ), 1) || '-' ||
                 
    SubStrDigitsIn_Date ), 5) || '-' ||
                 
    SubStrDigitsIn_Date ), 7) ) 
    Error Message: Syntax of date, time, or timestamp value not valid.

    What gives? Any help with this and/or a time function that does the same thing would be greatly appreciated.

    -R
    Last edited by FaStOnE; August 29, 2006, 08:03 AM.

  • #2
    Re: Create Function ... HELP

    You might want to throw some error handling in here to capture bad dates.

    PHP Code:


    CREATE 
    FUNCTION MYLIB.FMTDATE(In_Date Numeric(16))                 
    RETURNS DATE
    LANGUAGE SQL
    DETERMINISTIC
    RETURNS NULL ON NULL INPUT

    BEGIN
      
    RETURN ( SELECT DateSubStrCharIn_Date ), 1) || '-' || 
                 
    SubStrCharIn_Date ), 5) || '-' || 
                 
    SubStrCharIn_Date ), 7) ) FROM SYSIBM.SYSDUMMY1)      ;
    END;


    SELECT MYLIB.FmtDate(2006082900000000FROM SYSIBM.SYSDUMMY1
    Last edited by kpmac; August 29, 2006, 08:46 AM.
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

    Comment


    • #3
      Re: Create Function ... HELP

      Hi,

      you did not specify any parameter length, only data type!
      Your Statement may return in an string '0000-00-00' which is an invalid date.

      Just recreate your function as follows and try it again:

      PHP Code:
      Create Function FmtDate(In_Date Numeric(80))                
      Returns Date                                            
      Language SQL                                            
      Return DateSubStrDigitsIn_Date ), 1) || '-' ||
                   
      SubStrDigitsIn_Date ), 5) || '-' ||
                   
      SubStrDigitsIn_Date ), 7) ) 
      Birgitta

      Comment


      • #4
        Re: Create Function ... HELP

        I wouldnt have answered if I knew B. was signed on.
        Predictions are usually difficult, especially about the future. ~Yogi Berra

        Vertical Software Systems
        VSS.biz

        Comment


        • #5
          Re: Create Function ... HELP

          OK.. Just as I noticed B's answer I figured out the length piece. That works great on the Time Function, but the Date function still doesnt' work.

          This one works...
          PHP Code:
          create function FmtTimeT Numeric) )         
          returns Time                                      
          Language SQL                                      
          return TimeSubStrDigits), 1) || '.' ||
                       
          SubStrDigits), 3) || '.' ||
                       
          SubStrDigits), 5) ) 
          This one doesn't...
          PHP Code:
          Create Function FmtDateD Numeric) )         
          Returns Date                                      
          Language SQL                                      
          Return DateSubStrDigits), 1) || '-' ||
                       
          SubStrDigits), 5) || '-' ||
                       
          SubStrDigits), 7) ) 

          Comment


          • #6
            Re: Create Function ... HELP

            Is the session default date format set to *ISO ?

            Comment


            • #7
              Re: Create Function ... HELP

              OK.. in the momentus scheme of things that keeps this Web Site on the top of its game... I found the solutions and here they are...

              *Notes*
              These are simple solutions for one time situations and/or further use. These could be modified to accept a 2nd parameter that returns a value based on Format passed. I just didn't add that at this time.

              *Kudos*
              Thanks to Birgitta and KPMac for making do something outside the norm... THINK!

              PHP Code:
              Create Function FmtDateISOD Numeric) )                
              Returns Date                                             
              Language SQL                                             
              Begin                                                    
                
              Case When D <> 0 Then                                  
                     
              Return DateSubStrDigits), 1) || '-' ||
                                  
              SubStrDigits), 5) || '-' ||
                                  
              SubStrDigits), 7) );       
                Else                                                   
                     Return 
              Date'0001-01-01' );                      
                
              End Case;                                              
              End 
              PHP Code:
              Create Function FmtTimeISOT Numeric) )         
              Returns Time                                      
              Language SQL                           
              Return TimeSubStrDigits), 1) || '.' ||
                           
              SubStrDigits), 3) || '.' ||
                           
              SubStrDigits), 5) ) 
              Usage was like this ...

              PHP Code:
              Insert into MGC_TRAN AcctNoReq_AMReq_DTReq_Tm )                        
              Select AcctNoReq_AMFmtDateReq_DT ), FmtTimeISOReq_TM )               
                
              From GC_Tran
              Last edited by FaStOnE; August 30, 2006, 11:18 AM.

              Comment


              • #8
                Re: Create Function ... HELP

                Originally posted by FaStOnE View Post
                Thanks to Birgetta and KPMac for making do something outside the norm... THINK!
                [/PHP]
                I have looked my job description over very carefully and no where in it does it say I am to "think"

                I would think about the possibility of thinking but I'm not suppose to think
                I can't even think about the possibility, of thinking about the possibility of thinking... ect

                Comment

                Working...
                X