MySQL中CASE语句日期比较语法错误排查请求

Let's walk through the problems in your query and fix them step by step:

1. Direct Syntax Error (Causing the 1064 Error) #

The immediate issue is a missing comma in your SELECT clause. Between HIRE_DATE and the CASE statement, you forgot to add a comma—MySQL requires commas to separate every column (or calculated expression) in the SELECT list. That's exactly why you're seeing the syntax error pointing to the start of the CASE block.

2. Logical Overlap in CASE Conditions #

Even after fixing the syntax, your conditions have overlapping ranges that will break the expected behavior:

• The first condition HIRE_DATE < DATE('1987-09-01') already includes the entire range of your second condition (1987-05-01 to 1987-08-31), so the second WHEN will never execute.
• The third condition HIRE_DATE > DATE('1987-05-01') overlaps with both the first and second conditions, leading to inconsistent results.

Corrected Query #

Here's the fixed version with proper syntax and logical, non-overlapping conditions:

SELECT 
    EMPLOYEE_ID,
    FIRST_NAME,
    HIRE_DATE,
    CASE 
        WHEN HIRE_DATE < DATE('1987-05-01') THEN 'JUNIOR'
        WHEN HIRE_DATE BETWEEN DATE('1987-05-01') AND DATE('1987-08-31') THEN 'MID LEVEL SENIOR'
        WHEN HIRE_DATE >= DATE('1987-09-01') THEN 'SENIOR'
    END AS experience_level
FROM employees;

Key Improvements: #

• Added the missing comma after HIRE_DATE to fix the syntax error
• Rearranged conditions to check from earliest to latest dates, ensuring each range is mutually exclusive
• Used >= for the final condition to cover all dates on or after 1987-09-01 (no gaps in coverage)
• Removed single quotes around the alias experience_level—while MySQL allows single quotes here, using no quotes (for aliases without spaces) or backticks is more standard practice.

内容的提问来源于stack exchange，提问作者dipcb05