sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Conversion of date format

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

  • Conversion of date format

    Hi all,

    I have a big problem with a validation in JDe Development

    I have retrieved date from a table in the following format (YYWW): "0803" where 08 is the year(2008) and 03 corresponds to the 3rd week of the year.

    I have to convert it into the following format: DD-MM-YY. For the YY it corresponds to 08, thats ok. but for the month i must find out the 3rd week of the year corresponds to which month (basically its January ) and concerning the date, i have to set it to the date corresponding to the Monday of the 3rd week of year 08. i.e I know on calendar that the 3rd week of year 08 falls in the month of January and the monday of the 3rd week is the 21th. Hence my final conversion must look like that: 14-Jan-08. Its a YYWW to DD-MM-YY conversion.

    If you can help me on that I would be most grateful

    Thanks & Regards,
    Vashish

  • #2
    Re: Conversion of date format

    Been a while since JDE, but they do have some date routines.

    If you can't find one, I think the only way to do this is to create a calander file. With each record saying the date and what week of the year it is. This file would have to be created for going way way out into the future.

    I first thought about a simple WeekToMonth cross ref file, but this will not work for the day calculation.

    Good luck.
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: Conversion of date format

      this oughta be pretty darn close:
      Code:
           hoption(*nodebugio:*srcstmt) dftactgrp(*no) actgrp('ULTIPRO')
           d                 ds
           dWeekYear                        4s 0 Inz(0803)
           dYear                            2s 0 Overlay(WeekYear)
           dWeek                            2s 0 Overlay(WeekYear:*Next)
           dTestDate         s               d
           dCurrentDate      s               d
           dStartYearDate    s               d
           dDDMMYY           s              6s 0
           dCharDate         s              9a
           d                 ds
           dMonths                         36a   Inz('JanFebMarAprMayJunJulAugSep-
           d                                     OctNovDec')
           dMonth                           3a   Overlay(Months) Dim(12)
           * Retrieve System Values API Prototype
           dRtvSysVal        PR                  ExtPgm('QWCRSVAL')
           d  ReturnData                 5000a   Options(*VarSize)
           d  ReturnLength                 10i 0 Const
           d  NbrToRetrieve                10i 0 Const
           d  ValuesArray                  10a   Dim(10) Const
           d  ErrorCode                  5000a   Options(*VarSize)
      
           * Values Array
           d SysValues       s             10a   Dim(10)
      
           * ReturnData  DS
           d ReturnDS        DS                  Qualified
           d  NbrReturned                  10i 0
           d  Offsets                      10i 0 Dim(10)
           d  Data                       5000a
      
           * System Values Returned DS
           d RtnPtr          s               *   Inz(*NULL)
           d RtnSysVal       DS                  Qualified Based(RtnPtr)
           d  SysVal                       10a
           d  DataType                      1a
           d  InfoSts                       1a
           d  DataLength                   10i 0
           d  Data                       5000a
      
           * Information Status Constants
           d Available       c                   Const(' ')
           d Locked          c                   Const('L')
      
           * Data Type Constants
           d Character       c                   Const('C')
           d Binary          c                   Const('B')
           d UnAvailable     c                   Const(' ')
      
           dOutputData       s           5000a
           dreceivelen       s             10i 0
      
           dSysValue         s             10a
           d                 ds
           d StartDate                      8s 0
           d StartYear                      2s 0 Overlay(StartDate:3)
           d StartMon                       2s 0 Overlay(StartDate:*Next)
           d StartDay                       2s 0 Overlay(StartDate:*Next)
           d EndDate         s              8p 0
           d Day             s              5i 0
      
            //Array For Getting The Day Of The Week
           d                 ds
           d DaysOfTheWeek                 28a   Inz('*SUN*MON*TUE*WED*THU*FRI*SAT')
           d DayArray                       4a   Dim(7) Overlay(DaysOfTheWeek)
           dErrorDS          ds                  Qualified
           d BytesProvided                 10i 0 Inz(%Size(ErrorDS))
           d BytesAvailable                10i 0
           d MsgID                          7a
           d                                1a
           d Text                         500a   Varying
           dDays             s             10i 0
           dCharJan1         s              8a
            /free
      
              // Setup Current Year January 1
              CharJan1 = %Char(*Year) + '0101';
      
              CurrentDate = %Date();
              StartYearDate = %Date(CharJan1:*ISO0);
      
              //Get The Current Day Of The Week From System Values
              SysValues(1) = 'QDAYOFWEEK';
              RtvSysVal( ReturnDS
                       : %Size(ReturnDS)
                       : 1
                       : SysValues
                       : ErrorDS );
      
              //If Unable To Retrieve The System Value, Terminate
              If ErrorDS.BytesAvailable <> 0;
                ExSR Terminate;
              EndIf;
      
              //Get The Returned Data
              RtnPtr = %Addr(ReturnDS) + ReturnDS.Offsets(1);
      
              //Calculate The First Day Of The Pay Period (Will Be A Sunday)
              If %Subst(RtnSysVal.Data:1:RtnSysVal.DataLength) <> '*MON';
                 Day = %LookUp(
                       %Subst(RtnSysVal.Data:1:RtnSysVal.DataLength) :
                       DayArray);
                 Day -= 2;
               EndIf;
               If Day < 0;
                 Day = 0;
               EndIf;
      
               // To Calculate the number of days on a Monday
               // 7 * the value of Week is total number of days
               // to work with,  so we add 1
               // to set the date to 1 day after. subtract the number
               // of days to get a Monday date. then subtract 7 to
               // get the Monday for the desired week number.
               Days = ((((7 * Week) + 1) - Day) - 7);
               StartYearDate = StartYearDate + %Days(Days);
               TestDate = StartYearDate;
      
               // I used TestDate for debugging, etc.  i thought this
               //  would be harder and there's a lot of code i deleted LOL
               StartDate = %Dec(%Char(TestDate:*ISO0):8:0)                        ;
               CharDate = %EditC(StartDay:'X') + '-' + Month(StartMon) +
                         '-' + %EditC(StartYear:'X');
      
               ExSR Terminate;
      
               BegSR Terminate;
               *InLR = *On;
               Return;
               EndSR;
      you may have to tweak it some but i did some testing (not much) so give it a shot. if you do make any changes let me know LOL
      Last edited by tomholden; May 7th, 2008, 02:11 PM. Reason: added comments to code
      I'm not anti-social, I just don't like people -Tommy Holden

      Comment


      • #4
        Re: Conversion of date format

        Thanks guys for all the help :-).

        Comment


        • #5
          Re: Conversion of date format

          This is pretty @#%@#%@#%@#% rough and I am sure one of our resident SQL pros can whip it into shape and make it usable inside the SQLRPGLE script ...

          This works to return todays date 11/05/2008 as the start of week 20, so if we have 200820 as the YYYYWW parm, this will return 11/05/2008.

          I have used 20-1 where 20 is the week number returned.
          I have used 2008 where this is the year returned.

          Code:
          SELECT date('01/01/' || '2008')          
           + (                                     
               ((20-1)*7) -                            
               ( SELECT dayofweek('08/01/01')-1    
                   from SysIbm/SysDummy1 )         
             ) days                                
           as date FROM SysIbm/SysDummy1
          Strangely having the first line as SELECT date('01/01/' || '08') gives the wrong result ?

          Likewise having the 4th line as SELECT dayofweek('2008/01/01') gives '+++++++++++' as the result ?

          Go figure eh !

          I referenced Birgitta's post here for some hints on the WEEK function.
          Originally posted by Birgitta
          B.Hauser July 1st, 2006, 01:45 PM
          Hi,

          the easiest way to determine the week no. is to use the SQL scalar functions WEEK or WEEK_ISO.

          When using WEEK, a week starts with sunday and January, 1st is always in the first week of year.

          D MyDate S D inz(*Sys)
          D MyWeek S 2P 0
          *---------------------------------------------------------------
          C/EXEC SQL Set Option DatFmt = *ISO
          C/END-EXEC
          C/EXEC SQL Set :MyWeek = Week(:MyDate)
          C/END-EXEC
          C MyWeek Dsply
          C eval *InLR = *on


          According to the ISO Guidelines a week is defined as follows:
          1. A week always starts with monday
          2. The first week of year must have at least 4 days of the new year.
          --> January 4th is always in the first week of year
          --> The first thursday of a year is always in the first week of year.

          SQL provides the scalar function WEEK_ISO, that determines a week according to the ISO Guidelines:

          D MyDate S D inz(*Sys)
          D MyWeek S 2P 0
          *---------------------------------------------------------------
          C/EXEC SQL Set Option DatFmt = *ISO
          C/END-EXEC
          C/EXEC SQL Set :MyWeek = Week_ISO(:MyDate)
          C/END-EXEC
          C MyWeek Dsply
          C eval *InLR = *on


          Birgitta
          Now I am waiting for someone to "optimise" my scrawlings into minimilistic code .....

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

          Comment


          • #6
            Re: Conversion of date format

            Greg,

            Birgitta's gonna smack you if you continue using '||' 'cos it's not international. She's a tough cookie
            Regards

            Kit
            http://www.ecofitonline.com
            DeskfIT - ChangefIT - XrefIT
            ___________________________________
            There are only 3 kinds of people -
            Those that can count and those that can't.

            Comment


            • #7
              Re: Conversion of date format

              That's nothing Kit, it's mothers day in NZ, and I haven't got anything for my wife from the kids !!!

              Aside from that, I'm worried about Tom, he hasn't been a regular for that long on this forun and he's starting to post up responses that are longer than Jamie's, is there any therapy or something we can get him ?

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

              Comment


              • #8
                Re: Conversion of date format

                On a serious comment though, refer to Birgitta's post and there is a difference in WEEK() and WEEK_ISO() results, so it will matter what JDE uses as it's standard when it stores the week values in those fields.
                Greg Craill: "Life's hard - Get a helmet !!"

                Comment


                • #9
                  Re: Conversion of date format

                  Originally posted by gcraill View Post
                  That's nothing Kit, it's mothers day in NZ, and I haven't got anything for my wife from the kids !!!
                  It's been great having you around on the forum. Get well soon.
                  (unless you can pin it on the kids.)
                  Regards

                  Kit
                  http://www.ecofitonline.com
                  DeskfIT - ChangefIT - XrefIT
                  ___________________________________
                  There are only 3 kinds of people -
                  Those that can count and those that can't.

                  Comment


                  • #10
                    Re: Conversion of date format

                    How can I converrt Job date(*ISO) to ddmmyyyy?
                    Please help me on this...


                    many Thanks,
                    Bala.

                    Comment


                    • #11
                      Re: Conversion of date format

                      Please see this reference for lots of information on working with dates.

                      http://www.code400.com/funwithdates.php
                      Bill
                      "A good friend will bail you out of jail,
                      A true friend would be sitting beside you saying,
                      'Wow, that was fun.'"

                      Comment


                      • #12
                        Re: Conversion of date format

                        If you are looking for a numeric field.

                        D DateField S D
                        D ddmmyyyy S 8S 0
                        D
                        C Eval DateField = %date()
                        C Eval ddmmyyyy = %dec(Datefield:*EUR)

                        Comment


                        • #13
                          Re: Conversion of date format

                          I tire of the moronic JDE dates - I mean, like I just know that 114211 is today just by looking. Aren't these the dates on the calendar? No? So - I created some SQL functions - JDE2DATE that converts the JDE formatted date to ISO date so that those of us who live in the real world can know that 114211 is 2014-07-30 - and DATE2JDE that takes the real world date and converts it to the play world date of 114211 - so in my case SELECT WEEK(JDE2DATE(jdedatevalue)) FROM ... returns the week #.

                          Code:
                          /* Creating function QGPL.DATE2JDE */
                          CREATE or replace FUNCTION QGPL.DATE2JDE( DTE DATE ) RETURNS NUMERIC (6, 0)
                          LANGUAGE SQL
                          CONTAINS SQL
                          SPECIFIC QGPL.DATE2JDE_1
                          CONCURRENT ACCESS
                          RESOLUTION DEFAULT
                          FENCED
                          DETERMINISTIC
                          CALLED ON NULL INPUT
                          NO EXTERNAL ACTION
                          SET OPTION DBGVIEW = *SOURCE
                          begin
                          return ((year(dte) - 1900) * 1000) + dayofyear(dte);
                          end;;
                          
                          
                          /* Setting label text for QGPL.DATE2JDE */
                          LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_1 IS 'Date to JDE format';
                          
                          
                          /* Setting comment text for QGPL.DATE2JDE */
                          COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.DATE2JDE_1
                                     (DTE IS 'Date field' ) ;;
                          
                          
                          /* Creating function QGPL.DATE2JDE */
                          CREATE or replace FUNCTION QGPL.DATE2JDE( DTE VARCHAR(10))
                                      RETURNS  NUMERIC (6, 0)
                          LANGUAGE SQL
                          CONTAINS SQL
                          SPECIFIC QGPL.DATE2JDE_2
                          CONCURRENT ACCESS
                          RESOLUTION DEFAULT
                          FENCED
                          DETERMINISTIC
                          CALLED ON NULL INPUT
                          NO EXTERNAL ACTION
                          SET OPTION DBGVIEW = *SOURCE
                          begin
                          DECLARE WORKDATE DATE;
                          DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL;
                          SET WORKDATE = DATE(DTE);
                          return ((year(WORKDATE) - 1900) * 1000) + dayofyear(WORKDATE);
                          end;;
                          
                          
                          /* Setting label text for QGPL.DATE2JDE */
                          LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_2 IS 'Date to JDE format';
                          
                          
                          /* Creating function QGPL.DATE2JDE */
                          CREATE or replace FUNCTION QGPL.DATE2JDE( DTE NUMERIC(8,0),FMT VARCHAR(8))
                                 RETURNS  NUMERIC (6, 0)
                          LANGUAGE SQL
                          CONTAINS SQL
                          SPECIFIC QGPL.DATE2JDE_3
                          CONCURRENT ACCESS
                          RESOLUTION DEFAULT
                          FENCED
                          DETERMINISTIC
                          CALLED ON NULL INPUT
                          NO EXTERNAL ACTION
                          SET OPTION DBGVIEW = *SOURCE
                          begin
                          DECLARE STRWORKDATE CHAR(8);
                          DECLARE WORKDATE DATE;
                          DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN NULL;
                          SET STRWORKDATE = TRIM(CHAR(DTE));
                          IF FMT='YYYYMMDD' THEN
                          SET WORKDATE = DATE(SUBSTRING(STRWORKDATE,1,4) CONCAT '-' CONCAT
                             SUBSTRING(STRWORKDATE,5,2) CONCAT '-' CONCAT SUBSTRING(STRWORKDATE,7,2));
                          ELSE
                          set strworkdate = right(trim('00' concat strworkdate),8);
                          SET WORKDATE = DATE(SUBSTRING(STRWORKDATE,5,4) CONCAT '-' CONCAT
                             SUBSTRING(STRWORKDATE,1,2) CONCAT '-' CONCAT SUBSTRING(STRWORKDATE,3,2));
                          END IF;
                          return ((year(WORKDATE) - 1900) * 1000) + dayofyear(WORKDATE);
                          end;;
                          
                          
                          /* Setting label text for QGPL.DATE2JDE */
                          LABEL ON SPECIFIC ROUTINE QGPL.DATE2JDE_3 IS 'Date to JDE format';
                          
                          
                          /* Setting comment text for QGPL.DATE2JDE */
                          COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.DATE2JDE_2
                                 (DTE IS 'Char field' );
                          
                          
                          /* Creating function QGPL.JDE2DATE */
                          CREATE or replace FUNCTION QGPL.JDE2DATE( JDEDATE NUMERIC(6))
                          RETURNS  DATE
                          LANGUAGE SQL
                          SPECIFIC QGPL.JDE2DATE_1
                          CONTAINS SQL
                          CONCURRENT ACCESS
                          RESOLUTION DEFAULT
                          FENCED
                          DETERMINISTIC
                          CALLED ON NULL INPUT
                          NO EXTERNAL ACTION
                          SET OPTION DBGVIEW = *SOURCE,DATFMT=*ISO
                          begin
                          DECLARE DATEFLD DATE;
                          DECLARE INVALID_DATE CONDITION FOR SQLSTATE '22007';
                          DECLARE EXIT HANDLER FOR INVALID_DATE
                               RETURN  NULL;
                          SET DATEFLD = DATE(char(1900000 + JDEDATE));
                          return DATEFLD;
                          end;;
                          /* Setting label text for QGPL.JDE2DATE */
                          LABEL ON SPECIFIC ROUTINE QGPL.JDE2DATE_1 IS 'Date to JDE format';;
                          
                          
                          /* Setting comment text for RAMLIB.JDE2DATE */
                          COMMENT ON PARAMETER SPECIFIC ROUTINE QGPL.JDE2DATE_1
                              (JDEDATE IS 'Date field' );

                          Comment

                          sponsored links

                          Collapse

                          Working...
                          X