MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mySQLTable (invoice_date DATE);

INSERT INTO mySQLTable VALUES

(STR_TO_DATE("21/8/2567",'%d/%m/%Y')),

(STR_TO_DATE("13/9/2567",'%d/%m/%Y')),

(STR_TO_DATE("18/9/2567",'%d/%m/%Y')),

(STR_TO_DATE("25/10/2567",'%d/%m/%Y')),

(STR_TO_DATE("24/1/2568",'%d/%m/%Y'));

select * from mySQLTable;

Records: 11  Duplicates: 0  Warnings: 0

select

DATE_FORMAT(cast(invoice_date as date), '%d/%m/%Y') as invoice_date,

CONCAT("INV",

DATE_FORMAT(cast(invoice_date as date), "%y%m"),

"-",

row_number() OVER (PARTITION BY DATE_FORMAT(cast(invoice_date as date), "%y%m"))

) as invoice_no

from mySQLTable;