CODEWITHSUNDEEP
mysqlsqldatecase
HappyGoLucky5544
HappyGoLucky5544

Reputation: 15

CASE Statement between dates

For my EntryDate column I've been trying to successfully separate my data by month and into specific names '2013_04','2013_05' etc, so I can later group my data by it also. This code has thus far been unsuccessful, how can it be changed to work? 

CASE
WHEN EntryDate BETWEEN '2013-04-01' AND '2013-04-30' THEN '2013_04'
WHEN EntryDate BETWEEN '2013-05-01' AND '2013-05-31' THEN '2013_05'
WHEN EntryDate BETWEEN '2013-06-01' AND '2013-06-30' THEN '2013_06'
END

Thank you.

Upvotes: 0

Views: 2598

Answers (3)

Bill Gauvey
Bill Gauvey

Reputation: 105

DATE_FORMAT() is the best option, but you could use a various number if string functions and CONVERT

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I would use the year() and month() functions, just because this is common across databases:

CONCAT_WS('_', YEAR(EntryDate), MONTH(EntryDate))

However, DATE_FORMAT() is also a good solution.

Upvotes: 0

Oleg Komarov
Oleg Komarov

Reputation: 370

You can use DATE_FORMAT function:

DATE_FORMAT(EntryDate, '%Y_%m')

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

Upvotes: 1

Related Questions