This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 12, 2012 10:16 AM by 6363 RSS

Date Format Issue  converting from DATE to NVARCHAR2

kumar73 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    It looks that for many the millennium bug never happened :)

    Regards.
    Al
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points