Home » SQL & PL/SQL » SQL & PL/SQL » date format to dd-mm-yyyy
date format to dd-mm-yyyy [message #362400] Tue, 02 December 2008 03:42 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 #362412 is a reply to message #362400] Tue, 02 December 2008 03:57 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
please help me with the syntax to convert it into dd-mm-yyyy as its urgent
Re: date format to dd-mm-yyyy [message #362413 is a reply to message #362402] Tue, 02 December 2008 03:59 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

SQL> select trunc(sysdate) d1 from dual;

D1
---------
02-DEC-08

SQL> alter session set nls_date_format = 'mm-dd-yyyy';

Session altered.

SQL> select trunc(sysdate) d1 from dual;

D1
----------
12-02-2008

SQL> select to_date(to_char(trunc(sysdate),'mm-dd-yyyy'),'dd-mm-yyyy') d1 from dual;

D1
----------
02-12-2008

SQL> 

Is this what you want ?
You can change date format using nls_date_format.

Regards,
Ram.
Re: date format to dd-mm-yyyy [message #362414 is a reply to message #362400] Tue, 02 December 2008 04:06 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
Or by using a column of date type without altering the session and using nls_date_format.

Rajy
Re: date format to dd-mm-yyyy [message #362416 is a reply to message #362412] Tue, 02 December 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if it is urgent you can always refer to SQL Reference when we tell you the function you have to use.
Remember NOTHING is urgent on forum (but reading the rules).

Regards
Michel

[Updated on: Tue, 02 December 2008 04:11]

Report message to a moderator

Re: date format to dd-mm-yyyy [message #362422 is a reply to message #362400] Tue, 02 December 2008 04:15 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Yes thanks for the help .

i will abide by the rules of the forum
Re: date format to dd-mm-yyyy [message #362436 is a reply to message #362422] Tue, 02 December 2008 05:00 Go to previous messageGo to next message
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 #362477 is a reply to message #362400] Tue, 02 December 2008 07:13 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i tried to_char( ....,.....)..buts it says invalid month
Re: date format to dd-mm-yyyy [message #362484 is a reply to message #362477] Tue, 02 December 2008 07:27 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #362491 is a reply to message #362477] Tue, 02 December 2008 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
avrillavinge wrote on Tue, 02 December 2008 14:13
i tried to_char( ....,.....)..buts it says invalid month

If you read the documentation and follow it you can't have this error.

Regards
Michel

Re: date format to dd-mm-yyyy [message #363010 is a reply to message #362400] Thu, 04 December 2008 15:26 Go to previous messageGo to next message
mad_bu
Messages: 8
Registered: August 2006
Junior Member
Here is another answer for you question.
You can use this way too...

select to_date(sysdate,'dd-mm-yyyy') from dual

Hope it helps.
Re: date format to dd-mm-yyyy [message #363013 is a reply to message #363010] Thu, 04 December 2008 15:35 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4640
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


Previous Topic: SQL-PLUS FOREIGN KEY HELP!!
Next Topic: update
Goto Forum:
  


Current Time: Fri Dec 02 18:23:11 CST 2016

Total time taken to generate the page: 0.09392 seconds