CODEWITHSUNDEEP
mysqlsqlcasecase-when
memaxt
memaxt

Reputation: 57

MYSQL Case When simply not working

I'm trying to convert my TIMESTAMP to a string that represents the financial year period e.g. April to March

Here is my following statement:

SELECT Year, Type
CASE 
WHEN date_format(SubmissionDate, '%Y')='2015' AND MONTH(SubmissionDate)
BETWEEN 4 AND 12 THEN '15-16'
WHEN date_format(SubmissionDate, '%Y')='2016' AND MONTH(SubmissionDate)
BETWEEN 1 AND 3 THEN '15-16'
END as Year
FROM Incidents

Obviously it fails...I pretty knew when it comes to CASE statements, am I anywhere near?

Upvotes: 0

Views: 354

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I'm curious why you would mix functions like this. Why not do:

SELECT Year, Type,
       (CASE WHEN YEAR(SubmissionDate) = 2015  AND MONTH(SubmissionDate) BETWEEN 4 AND 12
             THEN '15-16'
             WHEN YEAR(SubmissionDate) = 2016 AND MONTH(SubmissionDate) BETWEEN 1 AND 3
             THEN '15-16'
        END) as Year
FROM Incidents;

Or, even more simply:

SELECT Year, Type,
       (CASE WHEN YEAR(DATE_SUB(SubmissionDate, INTERVAL 3 MONTH)) = 2015  
             THEN '15-16'
        END) as Year
FROM Incidents;

Upvotes: 0

Blank
Blank

Reputation: 12378

You've missed a , after Type:

SELECT Year, Type,
CASE 
WHEN date_format(SubmissionDate, '%Y')='2015' AND MONTH(SubmissionDate)
BETWEEN 4 AND 12 THEN '15-16'
WHEN date_format(SubmissionDate, '%Y')='2016' AND MONTH(SubmissionDate)
BETWEEN 1 AND 3 THEN '15-16'
END as Year
FROM Incidents

Upvotes: 1

Related Questions