Home » SQL & PL/SQL » SQL & PL/SQL » output of date format (merged)
output of date format (merged) [message #264207] Sun, 02 September 2007 00:47 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,
I have a date field as below
table test

bdate date;

bdate
------
12-OCT-1980
10-APR-1989
21-DEC-1992

Now I want the format as mm/dd/yyyy
so I tried as below

select to_char(bdate,'mm/dd/yyyy') x from test;

x
-------
10/12/1980
04/10/1989
12/21/1992

I think to_char function is of type string,but I want to return 
the output in the with date datatype and format as mm/dd/yyyy

so I used

select to_date(to_char(bdate,'mm/dd/yyyy'),'mm/dd/yyyy') from test;

bdate
------
12-OCT-1980
10-APR-1989
21-DEC-1992

I want in mm/dd/yyyy output and return type is date datatype.
how do I do this? when I use to_char then return type is string which I donot want.

thanks

[Updated on: Sun, 02 September 2007 00:50] by Moderator

Report message to a moderator

Re: output of date format [message #264209 is a reply to message #264207] Sun, 02 September 2007 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A date datatype has no (external) format.
You can't display or see a date.
You display and see a string.
So you have to convert your date to a string.
When you think you don't convert it (as in your first and last queries), you actually convert it using a default format.

Regards
Michel
Re: output of date format [message #264238 is a reply to message #264207] Sun, 02 September 2007 05:15 Go to previous messageGo to next message
guarav.sirsa
Messages: 8
Registered: September 2007
Location: Silvassa
Junior Member
Boss..Juz make fun for date..take variable and pass date into that var..n thn write ur query..this will give u ru result..this is simplest way..if anyone knows more simpler ...plzz
Re: output of date format [message #264240 is a reply to message #264238] Sun, 02 September 2007 05:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
guarav.sirsa wrote on Sun, 02 September 2007 12:15
Boss..Juz make fun for date..take variable and pass date into that var..n thn write ur query..this will give u ru result..this is simplest way..if anyone knows more simpler ...plzz

Just for fun: Read the Forum Guide. No IM speak. Type all characters of each word. Be clear: passing a DATE to a DATE variable won't help.

@stevefaulk: you could alter the default date format to 'dd/mm/yyyy' via an ALTER SESSION.

MHE
Re: output of date format [message #264241 is a reply to message #264240] Sun, 02 September 2007 05:34 Go to previous messageGo to next message
guarav.sirsa
Messages: 8
Registered: September 2007
Location: Silvassa
Junior Member
Thanks for your advice..you can try this query to get your result..Is it Fine now..
Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual
Re: output of date format [message #264251 is a reply to message #264209] Sun, 02 September 2007 06:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Yes, that is what Michel was hinting at:
Michel Cadot wrote on Sun, 02 September 2007 07:53
So you have to convert your date to a string.

MHE
Re: output of date format [message #264263 is a reply to message #264241] Sun, 02 September 2007 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you can try this query to get your result..Is it Fine now..
Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual


Not really fine:
SQL> Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual
  2  /
Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual
                        *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: output of date format [message #264394 is a reply to message #264263] Mon, 03 September 2007 03:20 Go to previous messageGo to next message
guarav.sirsa
Messages: 8
Registered: September 2007
Location: Silvassa
Junior Member
SQL> Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual
2 /

TO_CHAR((T
----------
31/08/2007
Re: output of date format [message #264398 is a reply to message #264394] Mon, 03 September 2007 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
guarav.sirsa wrote on Mon, 03 September 2007 10:20
SQL> Select to_char((to_date('31-Aug-07')),'DD/MM/YYYY') from dual
2 /

TO_CHAR((T
----------
31/08/2007

An example does not show it works.
A counter-example shows it does NOT work.

Regards
Michel

Re: output of date format [message #264749 is a reply to message #264207] Tue, 04 September 2007 07:54 Go to previous messageGo to next message
gnlnm
Messages: 1
Registered: August 2007
Junior Member
SELECT TO_CHAR(TO_DATE(bdate),'MM/DD/YYYY') FROM x;
Re: output of date format [message #264751 is a reply to message #264749] Tue, 04 September 2007 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
gnlnm wrote on Tue, 04 September 2007 08:54
SELECT TO_CHAR(TO_DATE(bdate),'MM/DD/YYYY') FROM x;


As Michel has shown, this is incorrect. You need to give a format for your TO_DATE function.
Re: output of date format [message #264777 is a reply to message #264207] Tue, 04 September 2007 10:09 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member

Need to give the format to the to_date function,how do I do this?As to_char returns string,which I don't require.

thanks
problem with to_date function [message #264778 is a reply to message #264207] Tue, 04 September 2007 10:11 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,
I have a date field as below
table test

bdate date;

bdate
------
12-OCT-1980
10-APR-1989
21-DEC-1992

Now I want the format as mm/dd/yyyy
so I tried as below

select to_char(bdate,'mm/dd/yyyy') x from test;

x
-------
10/12/1980
04/10/1989
12/21/1992

I think to_char function is of type string,but I want to return
the output in the with date datatype and format as mm/dd/yyyy

so I used

select to_date(to_char(bdate,'mm/dd/yyyy'),'mm/dd/yyyy') from test;

bdate
------
12-OCT-1980
10-APR-1989
21-DEC-1992

I want in mm/dd/yyyy output and return type is date datatype.
how do I do this? when I use to_char then return type is string which I donot want.

thanks
Re: problem with to_date function [message #264779 is a reply to message #264778] Tue, 04 September 2007 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Do NOT cross/multi-post!
http://www.orafaq.com/forum/t/88516/74940/
Re: output of date format (merged) [message #264850 is a reply to message #264207] Tue, 04 September 2007 16:15 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Your respose displayes a lack of understanding. A date is stored internally by oracle as a 7 byte string. wif you have a date column called my_date and you select


select my_date from dual;

Oracle will do an implicit conversion to string so that you can see the date, a binary dump would not be human readable. See the following


select dump(sysdate),sysdate,to_char(sysdate,'mm/dd/yyyy') 
from dual;

DUMP(SYSDATE)                      SYSDATE   TO_CHAR(SY
---------------------------------- --------- ----------
Typ=13 Len=8: 7,215,9,4,17,14,16,0 04-SEP-07 09/04/2007


They all show the date. The dump shows the internal structure, but they are all converted to a character format so that the human can read them.

[Updated on: Tue, 04 September 2007 16:16]

Report message to a moderator

Re: output of date format (merged) [message #264898 is a reply to message #264207] Tue, 04 September 2007 23:41 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
select to_char(sysdate,'mm/dd/yyyy') from dual returns the date as string type which I do not want.I need the date to be returned as
date only but in the format mm/dd/yyyy
so I used to_date function as below

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

my output is

TO_DATE(T
---------
05-SEP-07

I want it as 09/05/2007, I dont want to use to_char as the return type is string.I want the return type as date only

thanks
Re: output of date format (merged) [message #264899 is a reply to message #264207] Tue, 04 September 2007 23:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Are you clue resistant or clue repellant?
What part of Bill B's post did you not understand?
A date datatype does NOT contain any formatting; such as slash characters or the order of Year, Month or Day.
"09/05/2007" is a string; NOT a date!
In Oracle a DATE datatype includes a TIME component whether you want it or not.
What your "want" is the equivalent to me saying,
I want YOU to teach my pet pig to fly.

You can't get there from here. Deal with it!
Re: output of date format (merged) [message #264907 is a reply to message #264207] Wed, 05 September 2007 00:31 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you should deeper elaborate your requirements:
If you want to print a DATE column (as the provided select statement does), then WHY do you bother its type (as you print string on screen and you will not use it further)?
If you want to pass it as an argument, then WHY do you bother its format (as the recipient can output it in any format he chooses)?

Maaher wrote on Sun, 02 September 2007 12:23
you could alter the default date format to 'dd/mm/yyyy' via an ALTER SESSION.
Maybe you just want to change NLS_DATE_FORMAT of your session as Maaher pointed out.
Previous Topic: Oracle Date and Time Format in Cold Fusion
Next Topic: Help needed with Uni Assignment PLEASE!!!!
Goto Forum:
  


Current Time: Fri Dec 02 18:24:02 CST 2016

Total time taken to generate the page: 0.24073 seconds