Home » SQL & PL/SQL » SQL & PL/SQL » Date format
Date format [message #193771] Tue, 19 September 2006 06:15 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to format a date field into following format :
9/26/2006 12:00:00 AM

I tried using the following script :
TO_CHAR(scheddate,'fmmm/dd/yyyy hh:mi:ss AM')

But this is giving the output as :
9/27/2006 12:0:0 AM

Please advice.

Thanks,
Mona
Re: Date format [message #193774 is a reply to message #193771] Tue, 19 September 2006 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
fmmm/dd/yyyyfm hh24:mi:ss

  1* select to_char(trunc(sysdate),'fmmm/dd/yyyy hh24:mi:ss') from dual
SQL> /

TO_CHAR(TRUNC(SYSDA
-------------------
7/31/2006 0:0:0

SQL> ed
Wrote file afiedt.buf

  1* select to_char(trunc(sysdate),'fmmm/dd/yyyyfm hh24:mi:ss') from dual
SQL> /

TO_CHAR(TRUNC(SYSDA
-------------------
7/31/2006 00:00:00
Re: Date format [message #193775 is a reply to message #193774] Tue, 19 September 2006 06:32 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I have to get AM/PM at the end, so I need 12 hour date format.
Please advice.


Thanks,
Mona
Re: Date format [message #193776 is a reply to message #193775] Tue, 19 September 2006 06:36 Go to previous messageGo to next message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

select to_char(sysdate,'MM/DD/YYYY HH:MI:SS PM') from dual

Sandy
Re: Date format [message #193777 is a reply to message #193775] Tue, 19 September 2006 06:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, a more adventurous developer than you might have considered adding the extra FM to his own datemask, thus getting something like:
select to_char(trunc(sysdate),'fmmm/dd/yyyyfm hh:mi:ss AM') from dual;

7/31/2006 12:00:00 AM

But I guess that level of rash experimentation is discouraged at your company?

Laughing
Re: Date format [message #193778 is a reply to message #193777] Tue, 19 September 2006 06:41 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks JRow,
this worked.

Regards,
Mona
Previous Topic: Order by column value
Next Topic: select distinct top 5 sal
Goto Forum:
  


Current Time: Wed Dec 07 04:59:47 CST 2016

Total time taken to generate the page: 0.12461 seconds