CODEWITHSUNDEEP
pythonmysqlsql
bomotayo
bomotayo

Reputation: 1

Date_Format conversion in MySQL

I need help with converting Date of birth colmun on MySQL from '21-Aug-1990' format to '21-08-1990' format such that the data still retains dateTime format and I will be able to calculate an age column.

I have tried the following which worked. Now I want to calculate age from DOB.

case
when MID(avb.BVN_DOB,4,3) LIKE '%Jan%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'01')
when MID(avb.BVN_DOB,4,3) LIKE '%Feb%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'02')
when MID(avb.BVN_DOB,4,3) LIKE '%Mar%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'03')
when MID(avb.BVN_DOB,4,3) LIKE '%Apr%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'04')
when MID(avb.BVN_DOB,4,3) LIKE '%May%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'05')
when MID(avb.BVN_DOB,4,3) LIKE '%Jun%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'06')
when MID(avb.BVN_DOB,4,3) LIKE '%Jul%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'07')
when MID(avb.BVN_DOB,4,3) LIKE '%Aug%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'08')
when MID(avb.BVN_DOB,4,3) LIKE '%Sep%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'09')
when MID(avb.BVN_DOB,4,3) LIKE '%Oct%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'10')
when MID(avb.BVN_DOB,4,3) LIKE '%Nov%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'11')
when MID(avb.BVN_DOB,4,3) LIKE '%Dec%' then REPLACE(avb.BVN_DOB, MID(avb.BVN_DOB,4,3),'12') END AS DOB

Upvotes: 0

Views: 31

Answers (1)

Barmar
Barmar

Reputation: 782407

Use STR_TO_DATE() to parse the date and DATE_FORMAT() to convert it to the desired format.

DATE_FORMAT(STR_TO_DATE(avb.BVN_DB, '%d-%m-%Y'), '%d-%b-%Y') AS DOB

Upvotes: 1

Related Questions