Home » SQL & PL/SQL » SQL & PL/SQL » to_date of timestamp field
to_date of timestamp field [message #347652] Fri, 12 September 2008 10:10 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member

Hi,

I am trying to convert the below into date format.

12-SEP-08 04.22.03.885679 PM

However these commands gives me error date format not recognised.

SELECT to_date('12-SEP-08 04.22.03.885679 PM','DD-MON-YY HH24:MI:SS:SSSSSS') FROM DUAL

SELECT to_date('12-SEP-08 04.22.03.885679 PM','DD-MON-YY HH24.MI.SS.SSSSSS PM') FROM DUAL

Could you tell me what is wrong with the above?

Sharath
Re: to_date of timestamp field [message #347656 is a reply to message #347652] Fri, 12 September 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Copy and paste your session using SQL*Plus and formmatting
2/ Post your Oracle version with 4 decimals

SSSSSS format does not exist, just refer to SQL Reference with more than 100 posts I think you know where it is.

Regards
Michel

[Updated on: Fri, 12 September 2008 10:30]

Report message to a moderator

Re: to_date of timestamp field [message #347658 is a reply to message #347652] Fri, 12 September 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forgot to mention, when you will read SQL Reference, have a look at DATE datatype definition.

Regards
Michel

[Updated on: Fri, 12 September 2008 10:33]

Report message to a moderator

Re: to_date of timestamp field [message #347660 is a reply to message #347652] Fri, 12 September 2008 10:32 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
why are you (ab)using TO_DATE on a TIMESTAMP datatype?
Re: to_date of timestamp field [message #347661 is a reply to message #347652] Fri, 12 September 2008 10:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Could you tell me what is wrong with the above?

Unfortunately, DATE data type can store dates only with second precision; it cannot store fractional seconds. For storing fractional seconds, you have to use TIMESTAMP data type instead.
Additionally, fractions of seconds do not have SSSSSS format.
All is documented in SQL Reference, found with many other documentation books e.g. on http://tahiti.oracle.com/.

[Edit: too slow]

[Updated on: Fri, 12 September 2008 10:34]

Report message to a moderator

Re: to_date of timestamp field [message #347679 is a reply to message #347661] Fri, 12 September 2008 12:56 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member


I need to convert timestamp to date only to obtain the date part of the data in the timestamp data type field.

How could I do that?

This is how data is stored in the timestamp field
12-SEP-08 04.22.03.885679 PM

I need it in 12/09/08 format.
Re: to_date of timestamp field [message #347680 is a reply to message #347652] Fri, 12 September 2008 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>This is how data is stored in the timestamp field
>12-SEP-08 04.22.03.885679 PM
NO, this is how the value is DISPLAYED!

try TRUNC() function

[Updated on: Fri, 12 September 2008 12:59] by Moderator

Report message to a moderator

Re: to_date of timestamp field [message #347684 is a reply to message #347679] Fri, 12 September 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still no Oracle version
Still not following the guidelines
Still not test case.
Still not reading the SQL Reference

Well, why should we answer?

Regards
Michel
Re: to_date of timestamp field [message #347698 is a reply to message #347680] Fri, 12 September 2008 23:01 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Thanks a lot anacedent, trunc() worked.

Will follow the guidelines in my next post. SOrry!
Re: to_date of timestamp field [message #347700 is a reply to message #347698] Fri, 12 September 2008 23:09 Go to previous message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
hedonist123 wrote on Fri, 12 September 2008 21:01

Will follow the guidelines in my next post. SOrry!


After 100+ posts, you do not get a second chance to make a first impression.
You're On Your Own (YOYO)!
Previous Topic: SQL Query
Next Topic: storing a image file in a table
Goto Forum:
  


Current Time: Mon Dec 05 15:09:48 CST 2016

Total time taken to generate the page: 0.09570 seconds