OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » date function (oracle 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date function [message #484486]

Wed, 01 December 2010 01:02

shaz
Messages: 182
Registered: June 2009

Senior Member

hello gurus,

i want to show the date exactly in following format:

00:16:15 Wed 1st Dec 2010

I have tried :

select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Day dd Mon yyyy')
from dual

Result it is giving
00:16:15 Wednesday 01 Dec 2010

Please help:
How to show date in form 1st 2nd 3rd and so on?
How to show Abbreviated name of the day?

Report message to a moderator

Re: date function [message #484489 is a reply to message #484486]

Wed, 01 December 2010 01:14

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Dy fmddth Mon yyyy')
  2  from dual;
TO_CHAR(TO_DATE('01/12/20
-------------------------
00:16:15 Wed 1st Dec 2010

Regards
Michel

Report message to a moderator

Re: date function [message #484491 is a reply to message #484489]

Wed, 01 December 2010 01:19

shaz
Messages: 182
Registered: June 2009

Senior Member

Thanks this is what i was looking for.

Report message to a moderator

Re: date function [message #484504 is a reply to message #484491]

Wed, 01 December 2010 03:05

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

If you read the documents of Oracle
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#SQLRF51086
You can get it .

"Oracle 6i"? Is this date format for reports/forms 6i?

sriram

Report message to a moderator

Re: date function [message #484505 is a reply to message #484504]

Wed, 01 December 2010 03:14

shaz
Messages: 182
Registered: June 2009

Senior Member

Thanks Sriram for the link.
I am using it in form.

I want to read the date from the same format.

I am using:

select to_date ('00:16:15 Wed 10th Dec 2010','hh24:mi:ss Dy fmddth Mon rrrr')
from dual

This code is giving error. Please help.

[Updated on: Wed, 01 December 2010 04:08]

Report message to a moderator

Re: date function [message #484523 is a reply to message #484505]

Wed, 01 December 2010 04:34

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What error?

Report message to a moderator

Re: date function [message #484525 is a reply to message #484523]

Wed, 01 December 2010 04:37

shaz
Messages: 182
Registered: June 2009

Senior Member

ORA-01861: literal does not match format string

Don't know what I am missing Sad

Report message to a moderator

Re: date function [message #484528 is a reply to message #484525]

Wed, 01 December 2010 05:00

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

It wont identify your first argument 'th'.
If you remove it it will give you ORA-1835

Because Its a Friday not Wed.
/forum/fa/8498/0/

sriram

Attachment: Its FRiday.bmp
(Size: 425.96KB, Downloaded 1683 times)

Report message to a moderator

Re: date function [message #484529 is a reply to message #484525]

Wed, 01 December 2010 05:00

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ 10th Dec is not Wednesday but Friday
2/ I don't think you can use "th" format modifier in TO_DATE, only in TO_CHAR

Regards
Michel

Report message to a moderator

Re: date function [message #484539 is a reply to message #484529]

Wed, 01 December 2010 05:33

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_11gR2> create table test_tab as
  2  select to_char (sysdate + rownum - 1, 'hh24:mi:ss Dy ddth Mon rrrr') test_col
  3  from   dual
  4  connect by level <= 7
  5  /

Table created.

SCOTT@orcl_11gR2> select * from test_tab
  2  /

TEST_COL
--------------------------------------------
03:32:41 Wed 01st Dec 2010
03:32:41 Thu 02nd Dec 2010
03:32:41 Fri 03rd Dec 2010
03:32:41 Sat 04th Dec 2010
03:32:41 Sun 05th Dec 2010
03:32:41 Mon 06th Dec 2010
03:32:41 Tue 07th Dec 2010

7 rows selected.

SCOTT@orcl_11gR2> select to_date (substr (test_col, 1, 15) || substr (test_col, 18),
  2  		     'hh24:mi:ss Dy dd Mon rrrr')
  3  from   test_tab
  4  /

TO_DATE(S
---------
01-DEC-10
02-DEC-10
03-DEC-10
04-DEC-10
05-DEC-10
06-DEC-10
07-DEC-10

7 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: date function [message #484541 is a reply to message #484529]

Wed, 01 December 2010 05:41

shaz
Messages: 182
Registered: June 2009

Senior Member

ok I think we have to use the substr function to get the date.

Thank you all.
Smile

[Updated on: Wed, 01 December 2010 05:44]

Report message to a moderator

Re: date function [message #484610 is a reply to message #484541]

Thu, 02 December 2010 00:25

Frank
Messages: 7901
Registered: March 2000

Senior Member

That might not work for you, because you use fmddth, where Barbara used ddth for the day-of-month part. You will have to put some extra effort in it.
In short, human readable dates are not meant to be converted into machine readable dates. Can't you just use dd-mm-yyyy as input format? I bet most datatypers would rather type that than the extensive format you use.

Report message to a moderator

Re: date function [message #484619 is a reply to message #484610]

Thu, 02 December 2010 01:03

shaz
Messages: 182
Registered: June 2009

Senior Member

yes Frank I totally agree with you. But consequences are such that I had to use this format.

Report message to a moderator

Re: date function [message #484620 is a reply to message #484619]

Thu, 02 December 2010 01:16

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Another way is:

SQL> with
  2    data as (
  3      select to_char (sysdate + rownum - 3, 'hh24:mi:ss Dy fmddth Mon rrrr') test_col
  4      from   dual
  5      connect by level <= 7
  6    )
  7  select to_date(regexp_replace(test_col,'(th|st|nd|rd)',''),'hh24:mi:ss Dy dd Mon rrrr')
  8  from data
  9  /
TO_DATE(REG
-----------
30-NOV-2010
01-DEC-2010
02-DEC-2010
03-DEC-2010
04-DEC-2010
05-DEC-2010
06-DEC-2010

Regards
Michel

Report message to a moderator

Previous Topic:	Work on External File
Next Topic:	please help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 10:45:51 CDT 2025