ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Need SQL help to convert/check date

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

  • Need SQL help to convert/check date

    I am bout to read a file and fill a subfile using embedded SQL in RPG. Not sure how to convert date and check if date is greater than selected date (from input screen).

    Date format in file is 6 numeric (CYYDDD - some type of julian format, actually JDEdwards).

    Question:

    1. How to convert CYYDDD to MMDDYY
    2. How to test if date in file is greater than selected date by user (MMDDYY)

    Thank you.

  • #2
    Re: Need SQL help to convert/check date

    Try looking at this first.

    http://www.code400.com/funwithdates.php
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: Need SQL help to convert/check date

      Hiya,

      On the http://www.code400.com/funwithdates.php page suggested by DMW I found this from Pedro Molina ...


      --------------------------------------------------------------------------------
      First Posted by: Pedro Molina - Convertion from Julian Date to ISO
      All credit to Pedro Molina for the first post.
      --------------------------------------------------------------------------------

      Code:
      D ISODATE         S               D   DATFMT(*iso)
      D JULDATE         S              5S 0 INZ(05144) 
      /FREE
         ISODATE = %DATE(); // ISODATE RECEIVES THE SYSTEM DATE
         ISODATE = %DATE(JULDATE:*JUL0) // ISODATE RECEIVES JULDATE
      /END-FREE
      * THE NEW VALUE FOR ISODATE IS '2005-05-24'
      * JULDATE CONTAINS A JULIAN DATE FORMAT, 05 REFFERS TO THE YEAR AND 144 IS THE NUMBER OF TRANSCURRED DAYS.

      We can modify this to suit ...
      Code:
      D MDYDate         S               D   DATFMT(*MDY)                     
      D UsrDate         S               D   DATFMT(*MDY)                     
      D JulDate         S              5S 0                                  
      C     *Entry        PLIST                                              
      C                   PARM                    JulDate                    
      C                   PARM                    UsrDate                    
                                                                             
       /FREE                                                                 
         MDYDate = %DATE(JulDate:*JUL);   // MDYDate Receives the Julian Date
                                                                             
         Select;                                                             
         When UsrDate > MDYDate;                                             
          DSPLY 'User Date is Greater.';                                     
         When UsrDate = MDYDate;                                             
          DSPLY 'User Date is the Same.';                                    
         When UsrDate < MDYDate;                                             
          DSPLY 'User Date is Lesser.';                                      
         EndSl;                                                              
                                                                             
         *INLR = *ON;                                                        
       /END-FREE
      Call PGM(PgmName) PARM('06358' '12/25/06') and it seemed to work over here ...

      GC
      Greg Craill: "Life's hard - Get a helmet !!"

      Comment


      • #4
        Re: Need SQL help to convert/check date

        I would like to do all this through sql statement ie
        1. In the select statement check that jul date is within a selected date range (from/to) given by user in *ymd format
        and
        2. Also convert the julian date to *mdy format
        before writing to a sub file

        Comment


        • #5
          Re: Need SQL help to convert/check date

          I would like to do all this through sql statement ie
          1. In the select statement check that jul date is within a selected date range (from/to) given by user in *ymd format
          and
          2. Also convert the julian date to *mdy format
          before writing to a sub file

          Comment


          • #6
            Re: Need SQL help to convert/check date

            Here is a couple of SQL functions a co-worker wrote to convert JDE to data and back.
            PHP Code:
            /*  Convert date data type field to JDE julian CYYDDD   */               
            /*    Input field is date data type, length varies      */               
            /*      depending on date format setting: *ISO, *USA,   */               
            /*      *MDY, etc.                                      */               
            /*    Output field is numeric data type, length 6       */               
            /*  Execute the following command to create the function  */             
            /*  RUNSQLSTM SRCFILE(QGPL/QSQLSRC)                     */               
            /*            SRCMBR(FCNVDTEJDE)                        */               
            /*            COMMIT(*NONE)                             */               
            /*            DATFMT(*ISO)                              */               
                                                                                     
            CREATE FUNCTION QGPL/FCNVDTEJDE                                          
             
            (I_Date  DATE)                                                          
              
            RETURNS DECIMAL(6,0)                                                   
              
            LANGUAGE SQL                                                           
              BEGIN                                                                  
              
            DECLARE F_OUTPUT DECIMAL(6,0);                                         
              DECLARE 
            F_TEST INTEGER;                                                
              DECLARE CONTINUE 
            HANDLER FOR SQLEXCEPTION                              
                SET F_TEST
            =1;                                                        
              
            SET F_TEST=0;                                                          
              
            SET F_OUTPUT DECIMAL((YEAR(I_Date)-1900)*1000+DAYOFYEAR(I_Date),6);  
              IF 
            F_TEST=0 THEN                                 
             
            RETURN F_OUTPUT;            
            ELSE                         
             RETURN 
            NULL;                
             
            END IF;                     
            END 
            PHP Code:
            /*  Convert JDE julian date to date data type  */                    
            /*    Input field is numeric data type, length 6        */           
            /*    Output field is date data type, length varies     */           
            /*      depending on date format setting: *ISO, *USA,   */           
            /*      *MDY, etc.                                      */           
            /*  Execute the following command to create the function  */         
            /*  RUNSQLSTM SRCFILE(QGPL/QSQLSRC)                     */           
            /*            SRCMBR(FCNVJDEDTE)                        */           
            /*            COMMIT(*NONE)                             */           
            /*            DATFMT(*ISO)                              */           
                                                                                 
            CREATE FUNCTION QGPL/FCNVJDEDTE                                      
             
            (I_JDEJUL DECIMAL(6,0))                                             
              
            RETURNS DATE                                                       
              LANGUAGE SQL                                                       
              BEGIN                                                              
              
            DECLARE F_OUTPUT DATE;                                             
              DECLARE 
            F_TEST INTEGER;                                            
              DECLARE CONTINUE 
            HANDLER FOR SQLEXCEPTION                          
              SET F_TEST
            =1;                                                      
              
            SET F_TEST=0;                                                      
              IF 
            I_JDEJUL 0 THEN                                               
                SET F_OUTPUT 
            DATE(DIGITS(DECIMAL(I_JDEJUL+1900000,7,0)));      
             ELSE                                     
               
            SET F_OUTPUT DATE('2001-01-01');     
             
            END IF;                                  
             IF 
            F_TEST=0 THEN                         
              
            RETURN F_OUTPUT;                        
             ELSE                                     
              RETURN 
            NULL;                            
              
            END IF;                                 
             
            END 
            To call them you use it like this

            PHP Code:
            SELECT ONDTEJfcnvjdedte(ondtejFROM f00365 
            Never trust a dog to watch your food.

            Comment


            • #7
              Re: Need SQL help to convert/check date

              Nice function, sbp0718.! Since JDE dates are not "true" julian dates (as defined by IBM - not *JUL or *LONGJUL) they require special handling.

              Comment

              Working...
              X