Home » SQL & PL/SQL » SQL & PL/SQL » simple query. PLEASE GIVE ANSWER
simple query. PLEASE GIVE ANSWER [message #213345] Wed, 10 January 2007 07:00 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I have date as 4/1/2003 (MM/DD/YYYY)

I want to convert it to 20030401 (yyyymmdd)

Please guide. Its urgent

[Updated on: Wed, 10 January 2007 07:04]

Report message to a moderator

Re: simple query. PLEASE GIVE ANSWER [message #213348 is a reply to message #213345] Wed, 10 January 2007 07:06 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Read about to_char function in documentation or just google to_char.

[Updated on: Wed, 10 January 2007 07:08]

Report message to a moderator

Re: simple query. PLEASE GIVE ANSWER [message #213767 is a reply to message #213345] Fri, 12 January 2007 04:19 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member

Try it:-

Select to_char('4/1/2003','yyyyddmm') from dual;
Re: simple query. PLEASE GIVE ANSWER [message #213822 is a reply to message #213767] Fri, 12 January 2007 07:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
romi wrote on Fri, 12 January 2007 05:19

Try it:-

Select to_char('4/1/2003','yyyyddmm') from dual;


Why would you preform a TO_CHAR on a character string?
Re: simple query. PLEASE GIVE ANSWER [message #213843 is a reply to message #213767] Fri, 12 January 2007 08:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
romi wrote on Fri, 12 January 2007 05:19

Select to_char('4/1/2003','yyyyddmm') from dual;


As joy_division pointed out, why?

SQL> Select to_char('4/1/2003','yyyyddmm') from dual;
Select to_char('4/1/2003','yyyyddmm') from dual
               *
ERROR at line 1:
ORA-01722: invalid number

Re: simple query. PLEASE GIVE ANSWER [message #213902 is a reply to message #213345] Fri, 12 January 2007 13:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ajdams wrote on Fri, 12 January 2007 10:03
Before I help out I just want to say that if you don't know the correct answer don't post something and act like the person is an idiot for not knowing...I consistantly see more and more posts from people who think they are wonderful at whatever they do and really don't know anything about the computer science world. So if you have a degree from local-college.techinstitute.shitville don't post, anyway

There is a to_date('1/1/2007', 'MM/DD/YYYY) function
So in your case it'd be to_date('20070101', 'YYYYMMDD;)

HUH??? I'm not sure what your comment is in reference to, but I can assure you that experience has proven to me that a degree from whichever institution doesn't make the person. In fact, some of the smartest people I've ever met never went to college. To advise someone not to post because they didn't go to MIT is irreverent and just as disparaging as your first comment itself.

The correct answer was already provided by bonker and the answer you provided is still not the correct answer for the OP.

You can't decide how dates are stored in an Oracle table. Oracle uses its own internal format to store dates. From what I can gather from the OP, they want the date to appear as yyyymmdd when selected. Therefore, he should use to_char as bonker suggested or change the NLS_DATE_FORMAT. Using TO_DATE as you proposed will not help.

SQL> desc test_date
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 A1                                                 DATE

SQL> select * from nls_session_parameters
  2  where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                yyyymmdd

SQL> select a1 from test_date;

A1
--------
20030401

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

Session altered.

SQL> select a1 from test_date;

A1
----------
04/01/2003

SQL> select to_char(a1, 'yyyymmdd') from test_date;

TO_CHAR(
--------
20030401

The TO_DATE will not work:
SQL> select to_date(a1, 'yyyymmdd') from test_date;
select to_date(a1, 'yyyymmdd') from test_date
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>

BTW...welcome to the forum.
Re: simple query. PLEASE GIVE ANSWER [message #214249 is a reply to message #213345] Mon, 15 January 2007 09:45 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member

ebrian is correct and @ajdams please think twice before writing your own opinion ...

BTW from which University are you from ?

Ashu
Re: simple query. PLEASE GIVE ANSWER [message #214377 is a reply to message #214249] Tue, 16 January 2007 02:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@all: enough already. I cleaned the topic a little bit.

@rak007: Did you get your answer?

MHE
Re: simple query. PLEASE GIVE ANSWER [message #214410 is a reply to message #213345] Tue, 16 January 2007 05:56 Go to previous message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
----------THREAD CLOSED-----------
I have got the correct solution
PS: Please do not post any thing or any reply in this thread.
----------THREAD CLOSED-----------
Previous Topic: Query tune
Next Topic: Telephone problem
Goto Forum:
  


Current Time: Tue Dec 06 12:21:11 CST 2016

Total time taken to generate the page: 0.08387 seconds