ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to check for valid date??

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

  • How to check for valid date??

    IN SQL Server, I can use ISDATE to make sure a data field is valid. How can I do it in iSeries?

    Code:
    select date(replace(Substr(('01 12 06'), 1, 2 ) || '/' || Substr('09 25 06',3,3)  || '/' || '20' || Substr('09 25 06',6,3),' ',''))
     from table
    But still, how do I make sure it's a valid date ??
    Looked at the thread before mine but even in this thread , whatif the date is not valid?? How to validate a date??

    Last edited by ermagarden; January 22, 2007, 12:32 PM. Reason: Getting Closer

  • #2
    Re: How to check for valid date??

    I used a CASE statment : if year digit is 00 or month digit is 00 or day digit is 00, then i use a good date.

    Comment


    • #3
      Re: How to check for valid date??

      If you make the field a date field in the database then there should be some automatic validation built in?

      You can also use the "test" opcode to check it's a valid date in RPG before you try updating the database.
      Ben

      Comment


      • #4
        Re: How to check for valid date??

        I'm new to As400. I cant change the database...

        The field is character '01 12 06': then it has to be formatted and put in date field.

        Not using RPG either. Doing all in stored procs...

        Using CASE got me going for now.

        Thanks.

        Comment


        • #5
          Re: How to check for valid date??

          Ok fair enough, I've noticed as400 programmers at my place seem to fear new worldy technology's like dates and SQL lol
          Ben

          Comment


          • #6
            Re: How to check for valid date??

            hey it not as400 programmers that fear new stuff, i think most people are adverse to change, but it it most disturbing when there are people in IT that don't like to change.
            Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

            Comment


            • #7
              Re: How to check for valid date??

              I agree change is good...

              I'm from SQL Server/.Net world but learning AS400 at this new job of mine..

              Interesting to see the differences.

              Comment


              • #8
                Re: How to check for valid date??

                Yeah, I just finished uni where we mostly used oracle and Java. My job is mainly RPG though. At the moment were developing a web front end to an existing "legacy" CRM system using CGIDEV2. It's interesting, as you say, learning new things and there's a lot of new technology's like AJAX being used.

                Despite this though, some stuff that we took for granted at uni (such as database date fields and journalling) does seem to be frowned upon. This is re-enforced, reading as400 articles.
                Ben

                Comment


                • #9
                  Re: How to check for valid date??

                  The problem with assuming an numeric field contains month/day/year is just that. It is an assumption, that the data is within valid dates.

                  The easiest way is to create a user defined function to return a date from a decimal value. Then use date functions as needed.

                  Code:
                  Drop Function QGPL.DEC2DATE;
                  Create Function QGPL.DEC2DATE
                  (DateDec Decimal(8,0))
                      Returns Date
                  Language Sql
                  Deterministic
                  Contains SQL
                  Returns Null On Null Input
                  No External Action
                  Begin
                      Declare ReturnDate Date;
                  	Declare Exit Handler for SQLState '22007'
                  		Return Cast(Null As Date);
                  	Set ReturnDate = Date(
                  	Substr(Digits(DateDec),1,4) || '-' ||
                  	Substr(Digits(DateDec),5,2) || '-' ||
                  	Substr(Digits(DateDec),7,2));
                  	Return ReturnDate;
                  End
                  Select QGPL.DEC2DATE(20061215) - 10 days From SysIBM.SysDummy1

                  Will return back 2006-12-05 as a date.

                  Jim

                  Comment


                  • #10
                    Re: How to check for valid date??

                    Thanks. Good Idea to make it into a function.

                    Comment

                    Working...
                    X