Get month name from date in Oracle

Under the hood: Unraveling Oracle's date world

Here are some snazzy pointers to make your date work with Oracle as efficient as possible!

Tackling case sensitivity and unwanted white space

Remember, Oracle gives you freedom, but it also likes its case sensitive format models. Use 'Month' instead of 'MONTH':

-- The oracle respects your feelings, be sensitive here
SELECT TO_CHAR(your_date, 'Month') FROM your_table;  


Trust me on this, to lose the padding spaces that comes with MONTH, use the 'fm' format:

-- Let's trim that month's beard, neatness matters folks! 
SELECT TO_CHAR(your_date, 'fmMonth') FROM your_table;  


I don't have a table, now what?

Want to get the month name from a standalone date? That's what DUAL table is for:

-- Making dates without the 'table' romance
SELECT TO_CHAR(TO_DATE('15-11-2020', 'DD-MM-YYYY'), 'Month') FROM DUAL;  


Language, lingo, and locale

Month names change with locale! Set the NLS_DATE_LANGUAGE for specific language:

-- Oracle is multilingual, are you?
SELECT TO_CHAR(your_date, 'Month', 'NLS_DATE_LANGUAGE = English') AS month_name_english FROM your_table; 


Month in numbers

Want the numeric month representation? Use the EXTRACT function:

-- Extracting the month, no dentist required
SELECT EXTRACT(MONTH FROM your_date) AS month_number FROM your_table;  


Custom date strings

With custom date formats Oracle requires TO_DATE function:

-- Here comes another date, from another momma
SELECT TO_CHAR(TO_DATE('15-Nov-2020', 'DD-Mon-YYYY'), 'Month') FROM DUAL; 


Time components and your date

Time doesn't affect the extraction of the month name, but you know, your date could be hiding unexpected times:

-- Truncate and let the date be just a date
SELECT TO_CHAR(TRUNC(your_date), 'Month') FROM your_table;  


Error handling: Always Test

Not to sound like your mom, but always test your codes for different date inputs to avoid those nasty runtime errors.

Building up the Pace: Advanced use and customization

Getting the month name is the tip of the iceberg. There's a lot more you can do:

Shaping data for business intelligence

If you're a BI player, you know how important readability is for your reports. Go figure!

-- Sales report: 'Month-by-month, but with style'
SELECT TO_CHAR(SALE_DATE, 'Month') AS month_name, SUM(SALES) AS total_sales
FROM SALES_TABLE
GROUP BY TO_CHAR(SALE_DATE, 'Month')
ORDER BY EXTRACT(MONTH FROM SALE_DATE);


Dealing with leap years and unique cases

Handling leap years and date anamolies can be tricky. Always validate your date input to save you a headache later.

Cooking results for presentation layers

Preparing data for a presentation layer? Ensure your output fits the expected format:

-- What's your flavour, tell me what's your flavor, ooo!
SELECT INITCAP(TO_CHAR(your_date, 'fmMonth')) AS formatted_month_name FROM your_table;  
