IBMiAccess




Results 1 to 10 of 10

Thread: How to check for valid date??

  1. #1
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    786

    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??

    http://www.code400.com/forum/showthread.php?t=2964
    Last edited by ermagarden; January 22nd, 2007 at 01:32 PM. Reason: Getting Closer

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    786

    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.

  4. #3
    Analyst BenThurley's Avatar
    Join Date
    Dec 2006
    Location
    Southampton (UK)
    Age
    32
    Posts
    818
    Rep Power
    2534

    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.

  5. #4
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    786

    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.

  6. #5
    Analyst BenThurley's Avatar
    Join Date
    Dec 2006
    Location
    Southampton (UK)
    Age
    32
    Posts
    818
    Rep Power
    2534

    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

  7. #6
    Analyst DeadManWalks's Avatar
    Join Date
    Mar 2006
    Location
    Atlanta GA
    Posts
    1,598
    Rep Power
    3856

    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.

  8. #7
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    786

    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.

  9. #8
    Analyst BenThurley's Avatar
    Join Date
    Dec 2006
    Location
    Southampton (UK)
    Age
    32
    Posts
    818
    Rep Power
    2534

    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.

  10. #9
    Analyst
    Join Date
    Jan 2007
    Location
    Charlotte, NC
    Posts
    33
    Rep Power
    1218

    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

  11. #10
    Analyst
    Join Date
    Jul 2006
    Location
    USA
    Posts
    173
    Rep Power
    786

    Re: How to check for valid date??

    Thanks. Good Idea to make it into a function.

  12. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Difference between *OMIT and *NOPASS
    By emo in forum RPG/RPGLE
    Replies: 4
    Last Post: May 10th, 2008, 12:00 PM
  2. Retrieve system date from stored procedure
    By keanhoo168 in forum ANY THING GOES
    Replies: 3
    Last Post: February 13th, 2007, 11:05 PM
  3. yet another date issue
    By GLS400 in forum RPG/RPGLE
    Replies: 11
    Last Post: January 11th, 2007, 11:13 AM
  4. Need SQL help to convert/check date
    By snufse in forum SQL
    Replies: 6
    Last Post: December 28th, 2006, 09:48 PM
  5. File and dates
    By DeadManWalks in forum DB2
    Replies: 5
    Last Post: December 7th, 2006, 09:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •