casting 6 digit integer to date

Database
Enthusiast

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

3 REPLIES
Enthusiast

Re: casting 6 digit integer to date

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

Junior Contributor

Re: casting 6 digit integer to date

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

Enthusiast

Re: casting 6 digit integer to date

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