CODEWITHSUNDEEP
mysqldatecase
dipcb05
dipcb05

Reputation: 37

how do I compare a date in case statement in mysql

I want to compare date data and then assign a new name, here is my query

SELECT EMPLOYEE_ID,FIRST_NAME,HIRE_DATE
CASE
WHEN HIRE_DATE < DATE('1987-09-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-05-01') THEN 'SENIOR'
END AS 'experience_level' 
FROM employees;

there is some error that occurred. what is my problem? 

SQL query: Documentation


SELECT EMPLOYEE_ID,FIRST_NAME,HIRE_DATE
CASE
WHEN HIRE_DATE < DATE('1987-09-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-05-01') THEN 'SENIOR'
END AS 'experience_level' 
FROM employees LIMIT 0, 25
MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN HIRE_DATE < DATE('1987-09-01')THEN 'JUNIOR' WHEN HIRE_DATE BETWEEN D' at line 2

Upvotes: 0

Views: 643

Answers (1)

Nikhil Gyan
Nikhil Gyan

Reputation: 682

You're missing "," after "HIRE_DATE". i.e. your query should be like:

SELECT EMPLOYEE_ID,FIRST_NAME,HIRE_DATE,
CASE
  WHEN HIRE_DATE < DATE('1987-09-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-05-01') THEN 'SENIOR'
END AS 'experience_level' 
FROM employees;

Upvotes: 1

Related Questions