How to get month in string type from Date in SQL?-CodePudding

This is my SQL query.

select orderDate from orders;

From this, I can get output like this

03-OCT-96

How can I get a Month (like 'OCT') from the orderDate?

CodePudding user response：

try this:

select substring(cast(orderDate as string), 4, 7) as month from orders

CodePudding user response：

Perhaps the proper way to do this would be to first convert your date string to a bona fide date using STR_TO_DATE, then use DATE_FORMAT to extract out a month string in whatever format you want, e.g.

SELECT DATE_FORMAT(STR_TO_DATE('03-OCT-96', '%d-%b-%Y'), '%M')
-- October

Note that storing your dates as text in the format of %d-%b-%Y is generally bad practice. Consider using a formal date column, to avoid the costly conversion with STR_TO_DATE.