Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp date format conversion
Timestamp date format conversion [message #186683] Wed, 09 August 2006 01:09 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to change the format of timestamp date datatype from
8/9/2006 11:44:54 PM TO 8/9/06 23:44.
I have tried using TO_CHAR(timestamp,'MM/DD/RR HH24:MI')
But the output is "08/09/06 23:44"
How can I change the 08 and 09 to 8 and 9 respectively.
Please advice.
Re: Timestamp date format conversion [message #186694 is a reply to message #186683] Wed, 09 August 2006 02:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Use the FM (Fill Mode) format modifier
TO_CHAR(timestamp,'FMMM/DD/RR HH24:MI')


Ross Leishman
Re: Timestamp date format conversion [message #186699 is a reply to message #186694] Wed, 09 August 2006 02:34 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

This gives the result as "8/9/6 23:44" whereas I need the output as "8/9/06 23:44".

Please advice
Re: Timestamp date format conversion [message #186709 is a reply to message #186699] Wed, 09 August 2006 03:33 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
try
select to_char(sysdate,'fm mm/dd/"0"rr hh:mi') from dual;

Jim
Re: Timestamp date format conversion [message #186713 is a reply to message #186709] Wed, 09 August 2006 03:37 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks a lot. This worked.
Also is there any function in Oracle similar to the function ISDATE()?
Re: Timestamp date format conversion [message #186715 is a reply to message #186713] Wed, 09 August 2006 03:38 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
There are several posts relating to this question on these forums. Search for things like Is_Date

Jim
Re: Timestamp date format conversion [message #186736 is a reply to message #186715] Wed, 09 August 2006 04:56 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

For this I have to create a user function Is there no other way?
Re: Timestamp date format conversion [message #186740 is a reply to message #186736] Wed, 09 August 2006 05:08 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
That is the way to do it
Previous Topic: getting last day of the month - feedback asap pls
Next Topic: Creating sequences for each table in schema???
Goto Forum:
  


Current Time: Fri Dec 02 12:26:44 CST 2016

Total time taken to generate the page: 0.08010 seconds