IN SQL Server, I can use ISDATE to make sure a data field is valid. How can I do it in iSeries?
But still, how do I make sure it's a valid date ??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
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 22nd, 2007 at 01:32 PM. Reason: Getting Closer
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.
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.
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.
Ok fair enough, I've noticed as400 programmers at my place seem to fear new worldy technology's like dates and SQL lol
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.
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.
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.
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.
Select QGPL.DEC2DATE(20061215) - 10 days From SysIBM.SysDummy1Code: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
Will return back 2006-12-05 as a date.
Thanks. Good Idea to make it into a function.