Home » SQL & PL/SQL » SQL & PL/SQL » TIMESTAMP Conversion
TIMESTAMP Conversion [message #245987] Tue, 19 June 2007 08:55 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

Oracle Enterprise 10.2.0.3.0 running on Solaris 10.

I have a TIMESTAMP field and would like to strip the milliseconds off. I have tried using trunc but this will only take the TIMESTAMP back to minutes.

Here is an example of the column:-

22-OCT-97 00.00.01.15436

Here is what I require:-

22-OCT-97 00.00.01

I can use NLS_DATE_FORMAT on a session level but are there any SQL commands which may be useful - CAST, EXTRACT etc.

Thanks in advance,

Ken.
Re: TIMESTAMP Conversion [message #245992 is a reply to message #245987] Tue, 19 June 2007 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select to_timestamp('22-OCT-97 00.00.01.15436','DD-MON-YYYY HH24:MI:SS.FF') tim
  3    from dual
  4    )
  5  select tim+0 from data
  6  /
TIM+0
--------------------
22-OCT-0097 00:00:01

1 row selected.

Regards
Michel
Re: TIMESTAMP Conversion [message #245996 is a reply to message #245992] Tue, 19 June 2007 09:22 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thanks Michel. The solution you provide is not what I need though. I do not want the 'FF' on the end of the date format and if I miss this out and try to use TO_TIMESTAMP I receive the following error:-

select to_timestamp(********,'DD-MON-YYYY HH24:MI:SS') from ************;
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

TIA,

Ken.


Re: TIMESTAMP Conversion [message #246022 is a reply to message #245996] Tue, 19 June 2007 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You look at the wrong part of the query.
The "data" subquery is just there to build data that here a timestamp. Is this not your input?
This is the same as your table.

The main query (line 5) is a solution of what you want, as far as I understand it.

Regards
Michel
Re: TIMESTAMP Conversion [message #246176 is a reply to message #245987] Wed, 20 June 2007 03:40 Go to previous message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Michel,
You are correct.
Thanks for all your help,
Ken.
Previous Topic: PLS-00306: wrong number or types of arguments
Next Topic: How to break column values
Goto Forum:
  


Current Time: Sat Dec 03 15:52:26 CST 2016

Total time taken to generate the page: 0.12932 seconds