OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » query

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

query [message #7593]

Tue, 24 June 2003 01:07

rajool
Messages: 13
Registered: May 2003

Junior Member

Hi all,

i have one query.

i want to display the sysdate into date format.
like this 24/jun/2003 without changing the session settings. and also i don't want in to_char format.
i want in date format. in date datatype.

wat will be the query for this.

reply it soon.
Bye,

Report message to a moderator

Re: query [message #7595 is a reply to message #7593]

Tue, 24 June 2003 01:50

A1
Messages: 8
Registered: June 2003

Junior Member

The following is in Date format and not in char format.

select to_date(to_char(sysdate,'dd-mon-yyyy')) from dual

Hope you got it

Report message to a moderator

Re: query [message #7596 is a reply to message #7595]

Tue, 24 June 2003 02:23

Rahul
Messages: 94
Registered: December 1998

Member

Since u are changing to date ie the to_date( )
the result will be in dd-mon-yyyy not as dd/mon/yyyy.

Report message to a moderator

Re: query [message #7600 is a reply to message #7593]

Tue, 24 June 2003 09:53

Frank
Messages: 7901
Registered: March 2000

Senior Member

A date is a date is a date.
You cannot display a date in date format; sql converts it to characters to display it. (internally it is stored as a number or so).

So, your question does not compute.

hth
Frank

Report message to a moderator

Re: query [message #7609 is a reply to message #7593]

Tue, 24 June 2003 20:13

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

Senior Member

Although either altering the session and setting the nls_date_format or using to_char are the obvious simple solutions, if you are determined to do it the hard way, here is one method:

SQL> SELECT EXTRACT (DAY FROM SYSDATE)
  2         || '/'
  3         || DECODE (EXTRACT (MONTH FROM SYSDATE),
  4                    1, 'jan',
  5                    2, 'feb',
  6                    3, 'mar',
  7                    4, 'apr',
  8                    5, 'may',
  9                    6, 'jun',
 10                    7, 'jul',
 11                    8, 'aug',
 12                    9, 'sep',
 13                    10, 'oct',
 14                    11, 'nov',
 15                    12, 'dec')
 16         || '/'
 17         || EXTRACT (YEAR FROM SYSDATE)
 18         AS date_the_way_you_want
 19  FROM   DUAL
 20  /

DATE_THE_WAY_YOU_WANT
----------------------------------------------------------
24/jun/2003

Report message to a moderator

Previous Topic:	Schema and Instance
Next Topic:	problem with oracle installation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 15:20:09 CDT 2024