How to get month and year from date in SQL

Question

I need help to get month and year from a date which is in yyyymmdd format.

The dates look like this, '20150102', the output should be Jan 2015.

I want the output as on single value, currently using datepart function Im getting output in 2 different columns for month and year

Check Date_format() function: dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html — Madhur Bhaiya
– Madhur Bhaiya, Commented Jul 16, 2019 at 15:09

Khairul Alam · Accepted Answer · 2021-04-24 07:44:57Z

5

This may help:

SQL Server:

SELECT FORMAT (GETDATE(), 'MMM yyyy') -- Jul 2019 SELECT FORMAT (GETDATE(), 'MMMM yyyy') -- July 2019 SELECT RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8) -- Jul 2019

For more details: https://www.tutorialgateway.org/sql-date-format/

MySQL:

SELECT DATE_FORMAT("20150102", "%M %Y"); -- January 2015 SELECT DATE_FORMAT("20150102", "%b %Y"); -- Jan 2015 SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'

For more details: http://www.sqlines.com/mysql-to-oracle/date_format

edited Apr 24, 2021 at 7:44

answered Jul 16, 2019 at 15:21

Khairul Alam

1,3462 gold badges13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

priyesh a Over a year ago

thank you Khairul but this didnt work, I need the output for a date field which is in yyyymmdd format to give me the monthname and year

Khairul Alam Over a year ago

See second line, it's out is as your requirement

priyesh a Over a year ago

Actually, I guess the tag earlier was not specifying that this requirement is for SQL Server. Is that the reason this solution is not going through?

priyesh a Over a year ago

thanks Khairul, this works, however how would i use order by in such a case such that I get the ouput in chronological order. I want to see count of sales by this month year field, so it should give out output as Jan 2015 - 1000, Feb 2015 - 2000 and so on. Currently its giving Jan 2015 -1000, Jan 2016 -5000. Does that make sense?

priyesh a Over a year ago

I guess what im asking is how to write the order by clause here

|

Gordon Linoff · Accepted Answer · 2019-07-16 15:26:37Z

0

MySQL will recognize the date string as a date, so you can use date_format() to get the format you want:

select date_format('20150102', '%b %Y')

answered Jul 16, 2019 at 15:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

priyesh a Over a year ago

thanks Gordon, its giving me an error saying, DATE_FORMAT' is not a recognized built-in function name. Also, my requirement is not just for this particular field but for an entire column.

Power Mouse · Accepted Answer · 2019-07-16 19:17:28Z

0

there is a list of all differnet sql date formats sql date formats

answered Jul 16, 2019 at 19:17

Power Mouse

1,4511 gold badge14 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to get month and year from date in SQL

3 Answers 3

6 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Related