change order id after assign

[dennischan]

Hi,

I came across a headache and see if anyone would have idea on fixing it.

I change the starting order id daily from a cronjob because I want embed a date in order ID. For some reasons, many order id generated with wrong date and i hope to manually change them back to correct ones.

There must be available non-assigned order id for any day.

I believe it's possible but very complicated as the order id ( and other invoice, credit memo, delivery IDs etc ) has references other tables.

Will you have any idea or module or even script or SQL statement you know that I could use them to change order id manually?

Many thanks.

[amitsamsukha]

@dennischan ,

there are many extensions available like "Custom Order Number for Magento" which allows you to set a pattern for Order Increment ID. 

for already placed order you can create a 

MySQL such as 

UPDATE `sales_order`
SET    increment_id = Date_format(`created_at `, 'AMIT%Y%m%d%H%I%S')
WHERE  increment_id NOT LIKE "AMIT%"; 

this will set the order increment id to AMIT20210226001236

i.e. AMIT{YYYY}{MM}{DD}{H}{I}{S}

you can update the query as per your requirements. 

kindly Accept as a Solution if this works for you and give Kudos