All Forums Database
m_v_anita 9 posts Joined 07/12
14 Feb 2013
casting 6 digit integer to date

This is a situation i'm facing in production. one batch job has failed in the following query  
 
/***************************************************** ********/ 
 /* MAX-DATE-TBL - UPDATE 'AS-OF-DATE' WITH THE MAX DATE      */ 
 /*                FOR WHICH DATA IS AVAILABLE                */ 
 /***************************************************** ********/ 
 
INSERT INTO MAX_DATE_TMP         ( 
     MAX_MNTHYR_Y                                                    
     )                                                               
SELECT MAX(ABCD01_RPT_MNTHYR_Y)                                      
FROM   XYZPK01_STURSP_TBL                 M01                        
WHERE SUBSTR(CAST( M01.ABCD01_RPT_MNTHYR_Y  AS CHAR (10)),1,4)  ¦¦   
          SUBSTR(CAST( M01.ABCD01_RPT_MNTHYR_Y AS CHAR (10)),5,2)    
          = ( SELECT CYRMO_YYYYMM_Y                                  
              FROM  PUDBD05_WKYRMO_TBL                               
              WHERE WKYRMO_UPDATE_F = 'X'                            
               )                                                     
    ;                                                                
 *** Failure 2665 Invalid date.                                      
                Statement# 1, Info =0                                
 *** Total elapsed time was 8.81 seconds.  
 
The column MAX_MNTHYR_Y has DATE FORMAT 'YYYY-MM-DD'
and
ABCD01_RPT_MNTHYR_Y  is an integer ,value = 201,302  ie( 2013/Feb), my undertanding is that , the sql is tryng to move an integer value into a date field.. which is why it is failing. But this sql ran successfully till last month in production which is why i'm confused. Till last month this sql was inserting a value yyyy-mm-dd.
 
Please help me understand

14 Feb 2013

Hi Anita,
We can pass integer values to date columns, but they should result in valid dates
The dates are stored as integer as per the following logic
((Year - 1900) * 10000) + (Month * 100) + Day
For example 
If you were to pass the integer 1130228 then as per the above logic it would translate to 2013-02-28 which is a valid date, however if you try to pass 11302229 then it will fail as 2013-02-29 is not a valid date
So please make sure that the MAX(ABCD01_RPT_MNTHYR_Y) translates to a valid date.
Let me know if this solves the problem
 
Regards
R.Rajeev
 

dnoeth 4628 posts Joined 11/04
14 Feb 2013

It run successful before year 2013 :-)

select 201212 (date);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

    201212
----------
1920-12-12

You need to adjust the integer to the internal format, (year - 1900) * 10000 + month * 100 + day:

SELECT (201301 * 100) + 1 - 19000000 (DATE);

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

(((201301*100)+1)-19000000)
---------------------------
                 2013-01-01

Dieter

Dieter

m_v_anita 9 posts Joined 07/12
18 Feb 2013

Thanks Rajeev and Deiter it works. We changed the script in production.

You must sign in to leave a comment.