convert dd-mm date format to dd-month format in Mysql

Question

I have Employees date-of-birth in dd/mm format and from this details i need to find employee's whose birthday is today,tomorrow and day after tomorrow and the Emp_dob column must display date in this format(eg: Employee's birthday is today then 09-NOV).

below is mysql query

SELECT 

    Emp_Name,Emp_Department,

    Emp_DOB

    FROM mldob.tbl_mldobtest WHERE  

        Emp_DOB IN (

          DATE_FORMAT(CURDATE() + INTERVAL 1 DAY,'%d-%m'),

          DATE_FORMAT(CURDATE(),'%d-%m'),

          DATE_FORMAT(CURDATE()+INTERVAL 2 DAY,'%d-%m')

      )

      order by tbl_mldobtest.Emp_DOB;

the problem is EMP_DOB column displays dob in 09-11 format but i need in 09-Nov format

so can anybody please guide me how to achieve this.

thanks in advance.

Answer 1

EDIT: Sorry, didn't pay attention to DOB only holding day/month instead of full date.

Assuming Emp_DOB is CHAR(5) held as e.g. '09-11'

SELECT Emp_Name, Emp_Department, DATE_FORMAT(CONCAT(DATE_FORMAT(CURDATE(), '%Y'), '-', SUBSTRING(Emp_DOB,4,2), '-', SUBSTRING(Emp_DOB,1,2)), '%d-%b')
FROM tbl_mldobtest
WHERE Emp_DOB IN (
    DATE_FORMAT(CURDATE() + INTERVAL 1 DAY,'%d-%m'),
    DATE_FORMAT(CURDATE(),'%d-%m'),
    DATE_FORMAT(CURDATE()+INTERVAL 2 DAY,'%d-%m')
)
ORDER BY Emp_DOB;

If you hold Emp_DOB as a date (as I originally assumed) then this will do what you want, no need to format dates in where clause:

SELECT Emp_Name, Emp_Department, DATE_FORMAT(Emp_DOB, '%d-%b')
FROM tbl_mldobtest
WHERE DATE(Emp_DOB) BETWEEN DATE(CURDATE()) AND DATE(DATE_ADD(CURDATE(), INTERVAL 2 DAY))
ORDER BY Emp_DOB;

convert dd-mm date format to dd-month format in Mysql

Sign up