This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Dec 12, 2012 3:56 AM by Rahul_India Go to original post RSS
  • 15. Re: Date format
    kendenny Expert
    Currently Being Moderated
    976202 wrote:
    thank you for the patience!
    the manufacturing_date is varchar2 type
    OK. Big mistake but it was probably set up like that before you were involved. I understand that. So you currently have a varchar2 column which holds a date and it's in 'YYYY-MM-DD' format. I would recommend adding a new column to your table: manufacturing_date_dt which is a DATE datatype, then updating all rows in the table
    update my_table
       set manufacturing_date_dt = to_date(manufacturing_date,'YYYY-MM-DD');
    Once the data is in a date column you can display it in any format you like.
    If you keep it in a varchar2 column then the format you have is the best one to use. If you change it to MM-DD-YYYY and keep it in a varchar2 column then date comparisons will not work correctly as it will be comparing strings not dates and for strings '12-10-2012' > '01-01-2013'.

    Edited by: kendenny on Dec 11, 2012 4:58 AM
  • 16. Re: Date format
    Paul Horth Expert
    Currently Being Moderated
    976202 wrote:
    thank you for the patience!
    the manufacturing_date is varchar2 type
    Wrong, wrong wrong. Can you get your designer/DBA/whoever to change its type to DATE?
    It saves a lot of pain.
  • 17. Re: Date format
    979205 Newbie
    Currently Being Moderated
    thank u very very very much!!
    it works!! :)
  • 18. Re: Date format
    AlbertoFaenza Expert
    Currently Being Moderated
    Rahul_India wrote:

    I know it is a bad habit to store date in varchar2.But why.Tom kyte has iterated this statement in number of places.Its also bad to use YY. Explanation needed
    Rahul, if Tom Kyte has mentioned it several times you should have understood why.

    Check here below the reason why you should not store a date in VARCHAR2c(this is taken from Tom Kyte Blog
    <h3>How Data Integrity Decreases</h3>
    Using an incorrect datatype is wrong for many reasons, but the first and foremost is data integrity. Systems that use strings for dates or numbers will have some records with dates that are not valid and numbers that are not numbers. It is just the nature of the game here. If you permit any string in your date field, at some point, you will get dirty data in there.

    Without data-integrity rules in place, the integrity of your data is questionable. I’ve needed to write the functions to convert strings to dates but return NULL when the date won’t convert. I’ve also needed to try one of five date formats to see if I can get the date to convert. Can you look at 01/02/03 and tell what date that is? Is that yy/mm/dd, dd/mm/yy, or something else?
    And about this question:
    Its also bad to use YY.
    In fact if you store your date in a date data type you will never face this problem.

    Regards.
    Al
  • 19. Re: Date format
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul_India wrote:
    I know it is a bad habit to store date in varchar2.But why.Tom kyte has iterated this statement in number of places.Its also bad to use YY. Explanation needed
    You really need an explanation?

    Consider the following...
    SQL> select 'Wrong' from dual where '05-JAN-2010' > '01-DEC-2012';
    
    'WRON
    -----
    Wrong
    
    SQL> select 'Wrong' from dual where to_date('05-JAN-2010','DD-MON-YYYY') > to_date('01-DEC-2012','DD-MON-YYYY');
    
    no rows selected
    When dates are strings, you can get wrong results. When they're DATEs you get correct results.

    Likewise if you have data as strings and you get a date like '05/06/12' what is that date? Is it 5th June 2012? Is it 6th May 2012? Is it 12th June 2005? Is it 6th December 2005? etc. you just don't know for sure... even if there's other data of a similar format that may give a clue... you cannot be 100% certain. If it's stored as a DATE datatype you can't go wrong(*1) because the database stores dates in a known internal format, and how they are displayed is just a matter for the user interface.

    *1 ok, I could show an extreme example...
    SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
    
    Session altered.
    
    SQL> create table mydate (x date);
    
    Table created.
    
    SQL> create or replace function raw_date(p_in varchar2) return date is
      2    v_date date;
      3  begin
      4    dbms_stats.convert_raw_value(utl_raw.cast_to_raw(p_in), v_date);
      5    return v_date;
      6  end;
      7  /
    
    Function created.
    
    SQL> insert into mydate (x) values (raw_date(chr(120)||chr(100)||chr(255)||chr(255)||chr(1)||chr(1)||chr(1)));
    
    1 row created.
    
    SQL> select dump(x) from mydate;
    
    DUMP(X)
    ------------------------------------------------------------------------------------------------------------------
    Typ=12 Len=7: 120,100,255,255,1,1,1
    
    SQL> select x from mydate;
    
    X
    --------------------
    01-NOVEMBER-2000 00:
    
    SQL> insert into mydate (x) values (raw_date(chr(0)||chr(0)||chr(255)||chr(255)||chr(1)||chr(1)||chr(1)));
    
    1 row created.
    
    SQL> select x from mydate;
    ERROR:
    ORA-01801: date format is too long for internal buffer
    
    
    
    no rows selected
    but that really is forcing the database to store corrupt dates, bypassing it's own date validation.
  • 20. Re: Date format
    Rahul_India Journeyer
    Currently Being Moderated
    BluShadow wrote:
    Rahul_India wrote:
    I know it is a bad habit to store date in varchar2.But why.Tom kyte has iterated this statement in number of places.Its also bad to use YY. Explanation needed
    You really need an explanation?

    Consider the following...
    SQL> select 'Wrong' from dual where '05-JAN-2010' > '01-DEC-2012';
    
    'WRON
    -----
    Wrong
    
    SQL> select 'Wrong' from dual where to_date('05-JAN-2010','DD-MON-YYYY') > to_date('01-DEC-2012','DD-MON-YYYY');
    
    no rows selected
    When dates are strings, you can get wrong results. When they're DATEs you get correct results.

    Likewise if you have data as strings and you get a date like '05/06/12' what is that date? Is it 5th June 2012? Is it 6th May 2012? Is it 12th June 2005? Is it 6th December 2005? etc. you just don't know for sure... even if there's other data of a similar format that may give a clue... you cannot be 100% certain. If it's stored as a DATE datatype you can't go wrong(*1) because the database stores dates in a known internal format, and how they are displayed is just a matter for the user interface.

    *1 ok, I could show an extreme example...
    SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
    
    Session altered.
    
    SQL> create table mydate (x date);
    
    Table created.
    
    SQL> create or replace function raw_date(p_in varchar2) return date is
    2    v_date date;
    3  begin
    4    dbms_stats.convert_raw_value(utl_raw.cast_to_raw(p_in), v_date);
    5    return v_date;
    6  end;
    7  /
    
    Function created.
    
    SQL> insert into mydate (x) values (raw_date(chr(120)||chr(100)||chr(255)||chr(255)||chr(1)||chr(1)||chr(1)));
    
    1 row created.
    
    SQL> select dump(x) from mydate;
    
    DUMP(X)
    ------------------------------------------------------------------------------------------------------------------
    Typ=12 Len=7: 120,100,255,255,1,1,1
    
    SQL> select x from mydate;
    
    X
    --------------------
    01-NOVEMBER-2000 00:
    
    SQL> insert into mydate (x) values (raw_date(chr(0)||chr(0)||chr(255)||chr(255)||chr(1)||chr(1)||chr(1)));
    
    1 row created.
    
    SQL> select x from mydate;
    ERROR:
    ORA-01801: date format is too long for internal buffer
    
    
    
    no rows selected
    but that really is forcing the database to store corrupt dates, bypassing it's own date validation.
    Thanks BluShadow for an eye opener .
    May i know your name as mentioned in your passport ;)
  • 21. Re: Date format
    BluShadow Guru Moderator
    Currently Being Moderated
    Rahul_India wrote:
    Thanks BluShadow for an eye opener .
    May i know your name as mentioned in your passport ;)
    Blu Shadow

    Seriously... personal details are not available to the public, and you shouldn't even ask... and No, you can't have my credit card details either.
  • 22. Re: Date format
    Rahul_India Journeyer
    Currently Being Moderated
    lol k
1 2 Previous Next

Legend

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