Home » SQL & PL/SQL » SQL & PL/SQL » converting a string to date (oracle 10g)
converting a string to date [message #441276] Sat, 30 January 2010 02:05 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi ,

How to convert a string value 'Mar 22 2010 12:00:000A.M' into '22/03/20101 12:00A.M'

Thanks in Advance
Re: converting a string to date [message #441278 is a reply to message #441276] Sat, 30 January 2010 02:18 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
convert a string value 'Mar 22 2010 12:00:000A.M' into '22/03/20101 12:00A.M'


Your required output is also a string....

And '22/03/20101 12:00A.M' is that an Year?


sriram Smile
Re: converting a string to date [message #441280 is a reply to message #441278] Sat, 30 January 2010 03:12 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi

Sorry, a typo err..
I required output as '22/03/2010 12:00A.M'

Regards

Re: converting a string to date [message #441282 is a reply to message #441276] Sat, 30 January 2010 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference, Datetime Format Models

SQL> select to_char(to_date('Mar 22 2010 12:00:00A.M.','Mon DD YYYY HH:MI:SSA.M.'),
  2                 'DD/MM/YYYY HH:MIA.M.') the_date
  3  from dual;
THE_DATE
--------------------
22/03/2010 12:00A.M.

1 row selected.

Regards
Michel
Re: converting a string to date [message #441289 is a reply to message #441282] Sat, 30 January 2010 04:06 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi Michel

Thanks for the help.But, my input string is
'Mar 22 2010 12:00:000A.M'
Note: Millisec is also included in the input.

Required o/p:'22/03/2010 12:00A.M'
Re: converting a string to date [message #441309 is a reply to message #441289] Sat, 30 January 2010 08:46 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ramanajv1968
Required o/p:'22/03/2010 12:00A.M'

Michel's query result
22/03/2010 12:00A.M.

Perhaps I don't see well, but - Michel's solution looks like your "o/p" (saving disk space, eh?).

ramanajv1968
Note: Millisec is also included in the input.

So what? If you have any objections, there's a link to the documentation in Michel's message. Follow it, read, learn, and try to fix it by yourself (if you think that it should be improved).
Re: converting a string to date [message #441313 is a reply to message #441289] Sat, 30 January 2010 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramanajv1968 wrote on Sat, 30 January 2010 11:06
Hi Michel

Thanks for the help.But, my input string is
'Mar 22 2010 12:00:000A.M'
Note: Millisec is also included in the input.

If the final 000 are milliseconds, where are the seconds? or minutes if 00 are the seconds?

Regards
Michel

Re: converting a string to date [message #441517 is a reply to message #441276] Mon, 01 February 2010 14:07 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
If you are using milliseconds, do not use to_date, use to_timestamp function. Dates only go down to seconds, timestamps go down .000001 of a second.
Previous Topic: UNION
Next Topic: how to get the report with huge data in the tables
Goto Forum:
  


Current Time: Mon Dec 05 02:51:23 CST 2016

Total time taken to generate the page: 0.08674 seconds