1 2 Previous Next 16 Replies Latest reply: Dec 12, 2012 12:16 PM by 6363 RSS

    Date Format Issue  converting from DATE to NVARCHAR2

    kumar73
      Hello Friends,

      Here's the issue i am facing with dates conversion ..

      -- in source builddate format is DD-MM-YY and the data type is DATE
      select builddate from abc where vehiclecode in (
      'ZCBE53585',
      'ZCBE53561',
      'ZCBL53668',
      'HGMX43520')

      output :

      21-05-12
      21-05-12
      24-05-12
      22-10-44


      in target builddate type is NVARCHAR2 and trying to convert into YYYY-MM-DD format [ to store abc.builddate into xyz.builddate ]

      select TO_CHAR(TO_DATE(abc.builddate,'DD-MM-RR'),'YYYY-MM-DD') from xyz where vehiclecode in (
      'ZCBE53585',
      'ZCBE53561',
      'ZCBL53668',
      'HGMX43520')

      output :

      2012-05-21
      2012-05-21
      2012-05-24
      2044-10-22



      if you see the last output the date 22-10-44 should be 1944-10-22 which I want but the output shows as 2044-10-22 .

      This has become the big issue ..

      Please let me know how I can go about it ..

      thanks/kumar

      Edited by: kumar73 on 12 Dec, 2012 8:20 AM

      Edited by: kumar73 on 12 Dec, 2012 8:21 AM
        • 1. Re: Date Format Issue  converting from DATE to NVARCHAR2
          hitgon
          select TO_CHAR(builddate,'YYYY-MM-DD') from xyz where vehiclecode in (
          'ZCBE53585',
          'ZCBE53561',
          'ZCBL53668',
          'HGMX43520')
          • 2. Re: Date Format Issue  converting from DATE to NVARCHAR2
            6363
            Oh boy.

            DATE data type does not have format.
            TO_DATE should not be used on DATE data type, that is a bug in your code.
            Remove TO_DATE.
            Read these

            http://edstevensdba.wordpress.com/category/nls_date_format/

            http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#SQLRF06132
            • 3. Re: Date Format Issue  converting from DATE to NVARCHAR2
              AlbertoFaenza
              kumar73 wrote:
              Hello Friends,

              Here's the issue i am facing with dates conversion ..

              -- builddate format is DD-MM-YY and the data type is DATE

              builddate type is NVARCHAR2 and trying to convert into YYYY-MM-DD format
              It seems you are a little confused on data type of buildate column. Date or NVARCHAR2?

              If it is NVARCHAR2 (which is really bad) how do you decide that 44 is 1944 and not 2044?

              When you use RR to format your year in TO_DATE, 50-99 are stored as 1950-1999, and dates ending in 00-49 are stored as 2000-2049.

              If buildate as input value is already date then you can simply use TO_CHAR to convert it.

              i.e.:
              select TO_CHAR(abc.builddate,'YYYY-MM-DD') from xyz ...
              Regards.
              Al

              Edited by: Alberto Faenza on Dec 12, 2012 5:27 PM
              • 4. Re: Date Format Issue  converting from DATE to NVARCHAR2
                kumar73
                I know - but the source data that is coming for me is of format DD-MM-YY

                Thanks/kumar
                • 5. Re: Date Format Issue  converting from DATE to NVARCHAR2
                  AlbertoFaenza
                  kumar73 wrote:
                  I know - but the source data that is coming for me is of format DD-MM-YY

                  Thanks/kumar
                  This cannot be true if source data is date data type.
                  What is your source data? An external table or a normal table?

                  Please post table description:
                  desc abc;
                  Regards.
                  Al
                  • 6. Re: Date Format Issue  converting from DATE to NVARCHAR2
                    kumar73
                    CREATE TABLE "ABC"
                    (
                    "BUILDDATE" DATE)

                    CREATE TABLE "XYZ"
                    (
                    "BUILD_DATE" NVARCHAR2(20))



                    I tried ..

                    select builddate, TO_CHAR(builddate,'YYYY-MM-DD') from abc where vehiclecode in (
                    'ZCBE53585',
                    'ZCBE53561',
                    'ZCBL53668',
                    'HGMX43520')


                    21-05-12     2012-05-21
                    21-05-12     2012-05-21
                    24-05-12     2012-05-24
                    22-10-44     2044-10-22

                    I want the last record to be of output : 1944-10-22

                    thanks

                    Edited by: kumar73 on 12 Dec, 2012 8:39 AM
                    • 7. Re: Date Format Issue  converting from DATE to NVARCHAR2
                      6363
                      kumar73 wrote:

                      I know - but the source data that is coming for me is of format DD-MM-YY
                      If it is a DATE which you also said, it is NOT in the format DD-MM-YY.

                      Just because it displays on your screen that way, does not mean it will display on a screen for anyone else that way and does not mean the DATE data is in that format.

                      Which you would know if you read the provided links.
                      SQL> create table t (d date);
                      
                      Table created.
                      
                      SQL> insert into t values (sysdate);
                      
                      1 row created.
                      
                      SQL> select d, to_char(d,'DD-MM-YY'), to_char(d,'fmMonth, DD, YYYY')
                        2  from t;
                      
                      D         TO_CHAR( TO_CHAR(D,'FMMONTH,
                      --------- -------- -------------------
                      12-DEC-12 12-12-12 December, 12, 2012
                      
                      SQL> alter session set nls_date_format = 'MM/DD/YYYY';
                      
                      Session altered.
                      
                      SQL> select d, to_char(d,'DD-MM-YY'), to_char(d,'fmMonth, DD, YYYY')
                        2  from t;
                      
                      D          TO_CHAR( TO_CHAR(D,'FMMONTH,
                      ---------- -------- -------------------
                      12/12/2012 12-12-12 December, 12, 2012
                      What format is the column D of DATE data type in?
                      • 8. Re: Date Format Issue  converting from DATE to NVARCHAR2
                        Frank Kulash
                        Hi,
                        kumar73 wrote:
                        Hello Friends,

                        Here's the issue i am facing with dates conversion ..

                        -- builddate format is DD-MM-YY and the data type is DATE
                        That's not possible. DATEs in Oracle do not have format. When the DATE is represented in a string, then the string has a format (such as DD-MM-YY).
                        By the way, using 2-digit years is simply asking for trouble. Always use 4-digit years.

                        Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data.
                        select builddate from abc where vehiclecode in (
                        'ZCBE53585',
                        'ZCBE53561',
                        'ZCBL53668',
                        'HGMX43520')

                        output :

                        21-05-12
                        21-05-12
                        24-05-12
                        22-10-44


                        builddate type is NVARCHAR2 and trying to convert into YYYY-MM-DD format

                        select TO_CHAR(TO_DATE(abc.builddate,'DD-MM-RR'),'YYYY-MM-DD') from xyz where vehiclecode in (
                        'ZCBE53585',
                        'ZCBE53561',
                        'ZCBL53668',
                        'HGMX43520')

                        output :

                        2012-05-21
                        2012-05-21
                        2012-05-24
                        2044-10-22



                        if you see the last output the date 22-10-44 should be 1944-10-22 which I want but the output shows as 2044-10-22 .

                        This has become the big issue ..
                        This is one of the poroblems with 2-digit years.
                        When working in the year 2012, 'YY' format creates a DATE where the first 2 digits of the year are 20; that is, '12' is assumed to mean '2012'.
                        When working in the year 2012, 'YY' format creates a DATE where the first 2 digits of the year are 19 for higher numbers, and 20 for lower numbers; that is '50' is assumed to mean 1950, but '49' is assumed to mean 2049. The cutoff is between 59 and 50, there's no way to change that.

                        If you want to assume that (for example) 2-digit years form 00 to 24 start with 20, but 2-digit years starting with 25 through 99 start with 19, then you can use a CASE expression, like this:
                        SELECT  TO_CHAR ( ADD_MONTHS ( TO_DATE ( abc.builddate
                                                            , 'DD-MM-YY'
                                                   )
                                            , CASE
                                                    WHEN  SUBSTR (abc.builddate, 7, 2) <= '24'
                                                        THEN  0
                                                  ELSE  -100 * 12
                                              END
                                            )
                                  )     AS yyyy_mm_dd
                        FROM    ...
                        The basic strategy is to assume everything will start with 20. Test for higher years (e.g., year '25' or above) and, if it is above the cutoff point, subtract 100 years (= -1200 months).

                        This shows one of the many reasons why storing date information in a string column is such a bad idea.
                        • 9. Re: Date Format Issue  converting from DATE to NVARCHAR2
                          kumar73
                          CREATE TABLE "ABC"
                          (
                          "BUILDDATE" DATE, VEHICLECODE VARCHAR2(50) )

                          CREATE TABLE "XYZ"
                          (
                          "BUILD_DATE" NVARCHAR2(20), VEHICLECODE VARCHAR2(50) )


                          I tried ..

                          select builddate, TO_CHAR(builddate,'YYYY-MM-DD') from abc where vehiclecode in (
                          'ZCBE53585',
                          'ZCBE53561',
                          'ZCBL53668',
                          'HGMX43520')


                          21-05-12 2012-05-21
                          21-05-12 2012-05-21
                          24-05-12 2012-05-24
                          22-10-44 2044-10-22

                          I want the last record to be of output : 1944-10-22 to store in ABC table

                          thanks
                          • 10. Re: Date Format Issue  converting from DATE to NVARCHAR2
                            John Spencer
                            As 3360 said, date datatype do not have a format, they can be displayed as anything you like.

                            In a sqlplus session do
                            alter session set nls_date_format = 'dd-mon-yyyy'
                            Then when you issue:
                            select builddate
                            from abc
                            where vehiclecode in ('ZCBE53585', 'ZCBE53561', 'ZCBL53668', 'HGMX43520')
                            The output will be:
                            21-May-2012
                            21-May-2012
                            24-May-2012
                            22-Oct-1944
                            Although, given your obvious misunderstanding about how dates work, I have to admit that I am partially guessing about the centuries.

                            You just need to use to_char with the appropriate format mask to make the date into a string.

                            John
                            • 11. Re: Date Format Issue  converting from DATE to NVARCHAR2
                              6363
                              kumar73 wrote:

                              select builddate, TO_CHAR(builddate,'YYYY-MM-DD') from abc where vehiclecode in (
                              'ZCBE53585',
                              'ZCBE53561',
                              'ZCBL53668',
                              'HGMX43520')
                              >
                              21-05-12     2012-05-21
                              21-05-12     2012-05-21
                              24-05-12     2012-05-24
                              22-10-44     2044-10-22

                              I want the last record to be of output : 1944-10-22
                              It appears whatever inserted the data had the same bug using TO_DATE on a DATE that your original query had and has corrupted the data if it really was supposed to 1944 and will need to be fixed.
                              update abc set builddate = add_months(builddate, -1200)
                              where bulddate = date '2044-10-22'
                              ... etc
                              • 12. Re: Date Format Issue  converting from DATE to NVARCHAR2
                                AlbertoFaenza
                                kumar73 wrote:
                                CREATE TABLE "ABC"
                                (
                                "BUILDDATE" DATE, VEHICLECODE VARCHAR2(50) )

                                CREATE TABLE "XYZ"
                                (
                                "BUILD_DATE" NVARCHAR2(20), VEHICLECODE VARCHAR2(50) )


                                I tried ..

                                select builddate, TO_CHAR(builddate,'YYYY-MM-DD') from abc where vehiclecode in (
                                'ZCBE53585',
                                'ZCBE53561',
                                'ZCBL53668',
                                'HGMX43520')


                                21-05-12 2012-05-21
                                21-05-12 2012-05-21
                                24-05-12 2012-05-24
                                22-10-44 2044-10-22

                                I want the last record to be of output : 1944-10-22 to store in ABC table

                                thanks
                                It all depends on your input data:
                                CREATE TABLE abc (builddate DATE);
                                
                                INSERT INTO abc VALUES (TO_DATE ('22-10-1944', 'DD/MM/YYYY'));
                                INSERT INTO abc VALUES (TO_DATE ('22-10-2044', 'DD/MM/YYYY'));
                                
                                SELECT * FROM abc;
                                
                                BUILDDATE
                                ---------
                                22-OCT-44
                                22-OCT-44
                                
                                SELECT builddate, TO_CHAR (builddate, 'YYYY-MM-DD') ndate FROM abc;
                                
                                BUILDDATE NDATE     
                                --------- ----------
                                22-OCT-44 1944-10-22
                                22-OCT-44 2044-10-22
                                Regards.
                                Al
                                • 13. Re: Date Format Issue  converting from DATE to NVARCHAR2
                                  John Spencer
                                  I swear that none of the other posts after the post I repleid to were there when I posted before :-)
                                  kumar73 wrote:
                                  CREATE TABLE "ABC"
                                  (
                                  "BUILDDATE" DATE)

                                  CREATE TABLE "XYZ"
                                  (
                                  "BUILD_DATE" NVARCHAR2(20))



                                  I tried ..

                                  select builddate, TO_CHAR(builddate,'YYYY-MM-DD') from abc where vehiclecode in (
                                  'ZCBE53585',
                                  'ZCBE53561',
                                  'ZCBL53668',
                                  'HGMX43520')


                                  21-05-12     2012-05-21
                                  21-05-12     2012-05-21
                                  24-05-12     2012-05-24
                                  22-10-44     2044-10-22

                                  I want the last record to be of output : 1944-10-22

                                  thanks

                                  Edited by: kumar73 on 12 Dec, 2012 8:39 AM
                                  Then you need to fix the broken dates in table ABC before you convert them. As Al said, always use 4 digit years.

                                  Depending what you consider a "correct" date, you can identify the incorrect ones with something like:
                                  select *
                                  from abc
                                  where builddate > <the latest build date you would reasonably expect> or
                                       builddate < <the earliest build date you would reasonably expect>
                                  John
                                  • 14. Re: Date Format Issue  converting from DATE to NVARCHAR2
                                    AlbertoFaenza
                                    It looks that for many the millennium bug never happened :)

                                    Regards.
                                    Al
                                    1 2 Previous Next