date format to dd-mm-yyyy [message #362400] |
Tue, 02 December 2008 03:42  |
avrillavinge
Messages: 98 Registered: July 2007
|
Member |
|
|
i am trying to convert a date .
the format of that date is mm-dd-yyyy in database
i want to change it to dd-mm-yyyy can anyone please provide a syntax for the same.
i tried to_date() alongwith to char but no luck
|
|
|
Re: date format to dd-mm-yyyy [message #362402 is a reply to message #362400] |
Tue, 02 December 2008 03:44   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | the format of that date is mm-dd-yyyy in database
|
Wrong. It is in internal format you don't need to know which one.
Quote: | i want to change it to dd-mm-yyyy can anyone please provide a syntax for the same.
|
Given the previous sentence, meaningless question.
Quote: | i tried to_date() alongwith to char but no luck
|
TO_CHAR is the function for you to see a date.
Regards
Michel
[Updated on: Tue, 02 December 2008 03:46] Report message to a moderator
|
|
|
|
|
|
|
|
Re: date format to dd-mm-yyyy [message #362436 is a reply to message #362422] |
Tue, 02 December 2008 05:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What you need to understand is that dates are stored internaly in oracle in a non-human-readable format.
Every time you see a date, it has been translated into a character string by an implicit or explicit to_char call.
Your original question is meaningles, as you cannot change the format that the date is held in.
By callinf To_Char with different format masks, you can change the format that it is displayed in.
If you select TO_CHAR(<date_field>,'dd-mm-yyyy') then you should see the ate field in the format you want.
|
|
|
|
Re: date format to dd-mm-yyyy [message #362484 is a reply to message #362477] |
Tue, 02 December 2008 07:27   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
avrillavinge wrote on Tue, 02 December 2008 14:13 | i tried to_char( ....,.....)..buts it says invalid month
|
What command did you exactly run?
What is the data type of the queried column?
If it is VARCHAR2 (bad idea by the way), you may abuse SUBSTR function together with string concatenation (CONCAT function or || operator). Do not be surprised with strange format of values with different format than the one you described.
|
|
|
|
|
Re: date format to dd-mm-yyyy [message #363013 is a reply to message #363010] |
Thu, 04 December 2008 15:35   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@mad_bu,
mad_bu wrote on Fri, 05 December 2008 02:56 |
select to_date(sysdate,'dd-mm-yyyy') from dual
|
What good will a TO_DATE Function do on a date field?
Regards,
Jo
|
|
|
Re: date format to dd-mm-yyyy [message #363016 is a reply to message #363013] |
Thu, 04 December 2008 15:45  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
joicejohn wrote on Thu, 04 December 2008 16:35 |
What good will a TO_DATE Function do on a date field?
Regards,
Jo
|
Worse than that, it doesn't even work.
FOO SCOTT>select to_date(sysdate,'dd-mm-yyyy') from dual;
select to_date(sysdate,'dd-mm-yyyy') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
|
|
|