RE: SQL0181 and the IBM Date Window...

Dane,

While the date data type itself handles from 0001-01-01 to 9999-12-31,
when converting to/from the date data type or using date literals, the
date format specified for the job comes into play by default.

Try adding:
set option datfmt=*ISO 

Right before the BEGIN


Note that you could simplify this by simply doing:

CREATE FUNCTION TEST/JDTODATE

 (JD INTEGER)

 RETURNS DATE

 LANGUAGE SQL

 SPECIFIC TEST/JDTODATE

 NOT DETERMINISTIC

 CONTAINS SQL

 CALLED ON NULL INPUT

 DISALLOW PARALLEL

 BEGIN

 DECLARE VAR1 DATE;

 IF JD = 0 THEN SET VAR1 = NULL;

 ELSE SET VAR1 = date(cast(JD as char(7)));

 END IF;

 RETURN VAR1;

 END

Note that in this case, the set option statement shouldn't be needed as
the DATE() function knows what to do with a 7 character string.

Lastly, you might what to consider some additional error handling.
Here's a version I would use.

create function CvtNumJULtoDate(indte Numeric(7,0)) returns Date 
language SQL                                                          
not fenced                                                            
deterministic no external action                                      
returns null on null input                                            
contains SQL                                                          
set option datfmt=*ISO                                                
begin                                                                 
  declare INVALID_DATETIME condition for '22007';                     
  declare exit handler for INVALID_DATETIME                           
    begin                                                             
      resignal sqlstate '01HD1'                                       
        set message_text = 'Invalid date/time replaced by NULL';      
      return NULL;                                                    
    end;                                                              
  return(                                                             
     case indte                                                  
      when 0 then NULL                                           
      when 9999999 then date('9999-12-31')                      
      else date(digits(indte))
     end                                                         
   );                                                            
end;                                                             

Note that the above assumes that an invalid data of 9999999 really means
use the highest possible date which would be 9999-12-31.

HTH,



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dane Cox
Sent: Friday, July 14, 2006 2:14 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL0181 and the IBM Date Window...

All,
 
The User Defined Function that follows is receiving an 
SQL0181 error..."
Value in date, time, or timestamp string not valid." when it 
is passed a
date that falls outside the IBM date window (1940-2039).  This is
confusing because a long julian date contains a full 4 digit 
year.  Why
would the date window even come into play on this?
 
If this is indeed a restriction of the DATE data type, does 
anyone have
any suggestions on how to convert a 7 digit long julian date 
(passed as
an integer) using SQL where we could put it into a function like this?
I didn't write the code below and I am not an SQL expert by any means,
so please go easy on me.
 
Best regards,
Dane
 
CREATE FUNCTION TEST/JDTODATE

 (JD INTEGER)

 RETURNS DATE

 LANGUAGE SQL

 SPECIFIC TEST/JDTODATE

 NOT DETERMINISTIC

 CONTAINS SQL

 CALLED ON NULL INPUT

 DISALLOW PARALLEL

 BEGIN

 DECLARE VAR1 DATE;

 IF JD = 0 THEN SET VAR1 = NULL;

 ELSE SET VAR1 = DATE(DAYS(CONCAT(CAST(INTEGER(JD)/1000 AS CHAR (4)),

  '-01-01'))+MOD(INTEGER(JD),1000) - 1);

 END IF;

 RETURN VAR1;

 END

 
NOTICE: This electronic mail message and any files 
transmitted with it are intended exclusively
for the individual or entity to which it is addressed. The 
message, together with any attachment, may contain 
confidential and/or privileged
information. Any unauthorized review, use, printing, saving, 
copying, disclosure 
or distribution is strictly prohibited. If you have received 
this message in error, please immediately
advise the sender by reply email and delete all copies.
-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.