but i am sorry it did not work tooDefine "it did not work".
In your original version, "march" is not a valid value for the format mask "mm".
SELECT * FROM nls_session_parameters;
semsem wrote:There was no need for the TO_CHAR around it, I think that was included for display purposes.
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 ?
but i am sorry it did not work too
Edited by: semsem on Apr 18, 2011 6:17 AM
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.
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
semsem wrote:Ah, I see that Dom is correct, I misread the original requirement.
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
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 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 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.
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