Home » SQL & PL/SQL » SQL & PL/SQL » DATE FORMAT (Oracle Database 10g Release 10.1.0.2.0)
DATE FORMAT [message #331963] Mon, 07 July 2008 00:09 Go to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Hi,

I have a query like :-

SELECT EXTRACT(MONTH FROM TRUNC(TO_DATE('20080301','YYYYMMDD'))) FROM DUAL;

It return value 3.coz it is the third month.
But I want to see the name of the month using the same query with some additional logic.Means it should return MARCH inspite of 3.
please give any suggetion.
Re: DATE FORMAT [message #331964 is a reply to message #331963] Mon, 07 July 2008 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>But I want to see the name of the month using the same query with some additional logic.Means it should return MARCH inspite of 3.


Then use a different mask than "MM"
Re: DATE FORMAT [message #331965 is a reply to message #331964] Mon, 07 July 2008 00:21 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
try this code
select to_char(sysdate,'month') d from dual
Re: DATE FORMAT [message #331969 is a reply to message #331963] Mon, 07 July 2008 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at TO_CHAR function and Datetime Format Models

Regards
Michel
Re: DATE FORMAT [message #331976 is a reply to message #331963] Mon, 07 July 2008 01:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
subhadip.chanda wrote on Mon, 07 July 2008 07:09
Hi,

I have a query like :-

SELECT EXTRACT(MONTH FROM TRUNC(TO_DATE('20080301','YYYYMMDD'))) FROM DUAL;

In addition to what's been said on how to get the month's name:
why would you trunc the date (which means: set the time-part to zero), if you will only need the month part anyway?
Re: DATE FORMAT [message #331977 is a reply to message #331976] Mon, 07 July 2008 01:39 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
actualy this field contains timestamp also.
Anyway I got the solution.
Thanks for the co-ordination..
Re: DATE FORMAT [message #331978 is a reply to message #331977] Mon, 07 July 2008 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
actualy this field contains timestamp also.

Whatever the time is this does not change the month. Wink

Regards
Michel
Re: DATE FORMAT [message #332333 is a reply to message #331963] Tue, 08 July 2008 04:02 Go to previous message
ziauldba
Messages: 55
Registered: January 2007
Location: Bangladesh
Member

pls Ses this link
http://www.psoug.org/reference/convert_func.html
Previous Topic: UNDOTB2 dba_free_space Increased (merged)
Next Topic: copy or move table
Goto Forum:
  


Current Time: Thu Dec 08 00:24:54 CST 2016

Total time taken to generate the page: 0.09395 seconds