This discussion is archived
8 Replies Latest reply: Apr 18, 2011 8:47 AM by newbi_egy RSS

TO_DATE FUNCTION

newbi_egy Explorer
Currently Being Moderated
hi every body

i am trying to convert this charachter ('25 march 2010') to ('dd mm yyyy')
i have written

select to_date('25 march 2010' , 'dd mm yyyy') from dual ;

and it did not work

once the error is (not a valid month)
another ( a non-numeric character was found where a numeric was expected)

if u can guide me to the correct sql code
thanks a lot
  • 1. Re: TO_DATE FUNCTION
    Saubhik Guru
    Currently Being Moderated
    SELECT TO_CHAR(TO_DATE('25 march 2010' , 'dd month yyyy'),'dd mm yyyy') 
    FROM dual ;
  • 2. Re: TO_DATE FUNCTION
    newbi_egy Explorer
    Currently Being Moderated
    thanks for your answer but can you explain why did you do that
    it is a date from the begining why you convert it to a date then to charachter ?
    thanks
    but i am sorry it did not work too

    Edited by: semsem on Apr 18, 2011 6:17 AM
  • 3. Re: TO_DATE FUNCTION
    Dom Brooks Guru
    Currently Being Moderated
    It's not a date to begin with - it's a string with a "date" format.

    So you need to convert a string to a date, giving the date format of the string.
    Oracle will often do it's best even if you give the wrong format for the value supplied but a) it can get it wrong and you get the wrong conversion and b) sometimes it can't do it.
    but i am sorry it did not work too
    Define "it did not work".
    What happened?

    Can you provide a standalone example?
    Can you also list out your specific NLS parameters?
    SELECT * FROM nls_session_parameters;
    In your original version, "march" is not a valid value for the format mask "mm".

    Dependening on your NLS settings "march" may not be valid for "Month.

    See date format documentation:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34924

    Edited by: Dom Brooks on Apr 18, 2011 2:21 PM
  • 4. Re: TO_DATE FUNCTION
    BluShadow Guru Moderator
    Currently Being Moderated
    semsem wrote:
    thanks for your answer but can you explain why did you do that
    it is a date from the begining why you convert it to a date then to charachter ?
    thanks
    but i am sorry it did not work too

    Edited by: semsem on Apr 18, 2011 6:17 AM
    There was no need for the TO_CHAR around it, I think that was included for display purposes.

    It does work with just the TO_DATE...
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT TO_DATE('25 march 2010' , 'dd month yyyy')
      2* FROM dual
    SQL> /
    
    TO_DATE('25MARCH2010
    --------------------
    25-MAR-2010 00:00:00
    The initial issue you had was simply that you specified the month as "mm" but according to your date string you have it in "month" format model.

    Look at the documentation for the different format models...

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924
  • 5. Re: TO_DATE FUNCTION
    Dom Brooks Guru
    Currently Being Moderated
    There was no need for the TO_CHAR around it, I think that was included for display purposes.
    See original requirement
    i am trying to convert this charachter ('25 march 2010') to ('dd mm yyyy')
    So, TO_CHAR required if default format mask is not "dd mm yyyy"
  • 6. Re: TO_DATE FUNCTION
    newbi_egy Explorer
    Currently Being Moderated
    thanks dom , thanks blu
    it really worked when i converted march to (مارس) because of nls parameters
    the date here is in arabic not in english

    and that is what i am talking about , there was no need to (to_char) .

    but the result was (25/03/10) not (25/03/2010) and i want it 2010 not 10
    why did that happen
    thanks to all of you
  • 7. Re: TO_DATE FUNCTION
    BluShadow Guru Moderator
    Currently Being Moderated
    semsem wrote:
    thanks dom , thanks blu
    it really worked when i converted march to (مارس) because of nls parameters
    the date here is in arabic not in english

    and that is what i am talking about , there was no need to (to_char) .

    but the result was (25/03/10) not (25/03/2010) and i want it 2010 not 10
    why did that happen
    thanks to all of you
    Ah, I see that Dom is correct, I misread the original requirement.

    The TO_DATE function will convert the string '25 march 2010' into a DATE datatype, and that is the best way to store dates on the database. However, when you come to display a date to a user it needs converting to a string again, because a DATE datatype is, internally, just a load of numbers....
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT DUMP(TO_DATE('25 march 2010' , 'dd month yyyy'))
      2* from dual
    SQL> /
    
    DUMP(TO_DATE('25MARCH2010','DDMO
    --------------------------------
    Typ=13 Len=8: 218,7,3,25,0,0,0,0
    If you just select a DATE datatype, then Oracle will use the NLS settings to determine how to display it to the user. If however, you want it in a particular format, then you need to convert it explicitly to a string using the TO_CHAR function and specifying the format string you want.
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT TO_CHAR(TO_DATE('25 march 2010' , 'dd month yyyy'),'DD MM YYYY')
      2* from dual
    SQL> /
    
    TO_CHAR(TO
    ----------
    25 03 2010
    If you don't use TO_CHAR, then it's an implicit conversion based on NLS settings. You should never rely on implicit conversions in applications, because sometimes the NLS settings may change and this could break you code.
  • 8. Re: TO_DATE FUNCTION
    newbi_egy Explorer
    Currently Being Moderated
    thanks every body

Legend

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