CODEWITHSUNDEEP
sqloracle-databasedate
Niraj Choubey
Niraj Choubey

Reputation: 4040

Get month name from date in Oracle

How to fetch month name from a given date in Oracle?

If the given date is '15-11-2010' then I want November from this date.

Upvotes: 72

Views: 488239

Answers (7)

Jimmy
Jimmy

Reputation: 1061

if you are taking system date:

--Full month name :
select to_char(trunc(sysdate,'MONTH'),'MONTH') as month from dual; --MARCH    
--Short month name:
select to_char(trunc(sysdate,'MON'),'MON') as month from dual; --MAR
--Month number:
select to_char(trunc(sysdate,'MM'),'MM') as month from dual;  --03

if you are taking a specific date:

--Full month's name:
select to_char(trunc(to_date('11-03-2021','DD-MM-YYYY'),'MONTH'),'MONTH') as month from dual; --MARCH
--Short month's name:
select to_char(trunc(to_date('11-03-2021','DD-MM-YYYY'),'MON'),'MON') as month from dual; --MAR
--Month's number:
select to_char(trunc(to_date('11-03-2021','DD-MM-YYYY'),'MM'),'MM') as month from dual;  --03

Upvotes: 5

Ali
Ali

Reputation: 469

Try this 

select to_char(SYSDATE,'Month') from dual;

for full name and try this

select to_char(SYSDATE,'Mon') from dual;

for abbreviation

you can find more option here:

https://www.techonthenet.com/oracle/functions/to_char.php

Upvotes: 1

I_am_Batman
I_am_Batman

Reputation: 915

In Oracle (atleast 11g) database :

If you hit

select to_char(SYSDATE,'Month') from dual;

It gives unformatted month name, with spaces, for e.g. May would be given as 'May '. The string May will have spaces.

In order to format month name, i.e to trim spaces, you need

select to_char(SYSDATE,'fmMonth') from dual;

This would return 'May'.

Upvotes: 21

2Rhino53
2Rhino53

Reputation: 109

If you are trying to pull the value from a field, you could use:

select extract(month from [field_name])
from [table_name]

You can also insert day or year for the "month" extraction value above.

Upvotes: 10

ASIK RAJA A
ASIK RAJA A

Reputation: 441

Try this,

select to_char(sysdate,'dd') from dual; -> 08 (date)
select to_char(sysdate,'mm') from dual; -> 02 (month in number)
select to_char(sysdate,'yyyy') from dual; -> 2013 (Full year)

Upvotes: 23

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

select to_char(sysdate, 'Month') from dual

in your example will be:

select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual

Upvotes: 105

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

to_char(mydate, 'MONTH') will do the job.

Upvotes: 21

Related Questions