Show month of a full date in SQL

Question

I have a column in my database with populated with full dates : (0000-00-00) I need to show this date as a month string (ex. January).

I have too choices on my SQL QUERY here:

1 create a case when month is '01' = 'January' (which I believe is the best option since I could then change the string however I want (In Portuguese, with the first letter capital for example). I was not able to do that, I tried substring, but dates don´t like substring statements.

2 just show that date as month:

MONTH(DT_CANCEL)

However, I need to show the string (January) and the above only outputs 01

What is the best option (and how) can I show the full month name of a date in a SQL QUERY?

Using MySQL.

score 1 · Accepted Answer · answered Dec 05 '18 at 10:59

1

You can simply use MonthName:

SELECT MONTHNAME('2009-05-18');

This will output May.

Reference here: https://www.w3resource.com/mysql/date-and-time-functions/mysql-monthname-function.php

answered Dec 05 '18 at 10:59

Gauravsa

6,330
2
21
30

This worked for me, now, is it possible to show only the three first letters? Jan ? instead of January? – ROCA Dec 05 '18 at 11:04
I just used: LEFT(MONTHNAME(DT_CANCEL), 3) , worked, thanks man! – ROCA Dec 05 '18 at 11:08
@Gauravsa SELECT MONTHNAME('2009-05-05'); In this statement, how to decide month . Format may be "yyyy-mm-dd" or "yyyy-dd-mm" – Santosh Vishwakarma Dec 05 '18 at 11:49
u Can use str_to_date function and then use monthname – Gauravsa Dec 05 '18 at 12:09
https://stackoverflow.com/questions/18597051/sql-date-format-convert-dd-mm-yy-to-yyyy-mm-dd – Gauravsa Dec 05 '18 at 12:10

score 1 · Answer 2 · answered Dec 05 '18 at 11:00

1

In mysql you can use monthname

SELECT MONTHNAME(DT_CANCEL) from your_table;

answered Dec 05 '18 at 11:00

ScaisEdge

131,976
10
91
107

score 1 · Answer 3 · edited Dec 05 '18 at 12:55

1

mysql:

select date_format('2018-12-01','%M'); /* prints December*/

select date_format('2018-12-01','%b'); /* prints Dec*/

edited Dec 05 '18 at 12:55

Gauravsa

6,330
2
21
30

answered Dec 05 '18 at 12:08

Luca Lupidi

164
5

score 0 · Answer 4 · answered Dec 05 '18 at 10:48

0

In Oracle, use TO_CHAR:

select TO_CHAR(SYSDATE, 'Month') FROM dual;

December

answered Dec 05 '18 at 10:48

Rob G

592
4
18

score 0 · Answer 5 · answered Dec 05 '18 at 10:54

If you are using MS SQL Server, you don't need to write a Case statement, try below:

SET LANGUAGE Portuguese

SELECT UPPER(LEFT(DATENAME(month, getdate()),1))+LOWER(SUBSTRING(DATENAME(month, 
getdate()),2,LEN(DATENAME(month, getdate()))))

If you want to reset language for your server, you can see the list of aliases by running the script below:

SELECT * FROM sys.syslanguages

Show month of a full date in SQL

5 Answers5