2 Replies Latest reply: Nov 21, 2012 10:36 AM by Frank Kulash RSS

    DATE FORMAT ISSUE !!

    kumar73
      Hello Friends,

      I have encountered a basic data format issue ..

      select BUILDDATE, TO_CHAR(TO_DATE(BUILDDATE,'DD-MM-YY'),'YYYY-MM-DD') VALIDFORMAT from CONSTRUCTION;

      BUILDDATE VALIDFORMAT
      18-05-99     2099-05-18
      29-04-98     2098-04-29
      28-04-11     2011-04-28
      09-11-05     2005-11-09
      The problem here is in the output 18-05-99 its refers to 1999-05-18 But when I convert into the validformat , It gives me as 2099-05-18 the same for second record which is incorrect , the third, fourth records are correct .

      How I can convert this kind of data to have similar format across ..

      Apprecaite your help .

      thanks/

      Edited by: kumar73 on 21 Nov, 2012 8:22 AM
        • 1. Re: DATE FORMAT ISSUE !!
          BluShadow
          If you are converting an input string to a date and that string has 2 digit years (shame on whoever did that), use RR instead of YY.
          • 2. Re: DATE FORMAT ISSUE !!
            Frank Kulash
            Hi,

            'YY' means the first 2 digits will be the same as the first 2 digits of the current year. If you run that code in 2012, then '99' will be taken as short for 2099.

            If you want '99' to mean 1999, then use 'RR' instead of 'YY'.
            TO_CHAR ( TO_DATE ( builddate
                          , 'DD-MM-RR'     -- Not 'DD-MM-YY'
                        )
                    , 'YYYY-MM-DD'
                 ) 
            This shows one of the many reasons why storing date information in a VARCHAR2 column is such a bad idea. If you use a DATE column, the year is always indicated precisely.