MySQL - How to convert date to the month has leading zeros?-CodePudding

So I'm trying to insert dates into a table and the date is in this format:

8/3/2021

However I want to add a leading 0 before the month and day so the date shows 08/03/2021. Also I want to add it as a string concatenated with another string so test123-08/03/2021

CodePudding user response：

You should be inserting your source dates into a proper date or datetime columm. Then, to view your dates in the format you want, use the DATE_FORMAT() function with the appropriate format mask:

SELECT DATE_FORMAT(date_col, '%d/%m/%Y') AS date_out
FROM yourTable;

CodePudding user response：

If you really store date in that format then you may try this:

SELECT 
       DATE_FORMAT(STR_TO_DATE(date_col_string,'%d/%m/%Y'),'%d/%m/%Y') as 'zero-padded',
       CONCAT(string_val,'-',DATE_FORMAT(STR_TO_DATE(date_col_string,'%d/%m/%Y'),'%d/%m/%Y')) as 'concatenated'
FROM mytable;

Use STR_TO_DATE() function to change the date value to standard MySQL date format of YYYY-MM-DD then use DATE_FORMAT() function to display the date value as per your desired output. The second operation is adding CONCAT() function on the converted date with your selected string. I'm assuming that your date value is d/m/y, because as @Stu mentioned in the comment, since you're not storing as MySQL standard date format, that means 8/3/2021 can be either d/m/y or m/d/y. With a standard date format value, the query would be shorter:

SELECT 
       DATE_FORMAT(date_col,'%d/%m/%Y') as 'zero-padded',
       CONCAT(string_val,'-',DATE_FORMAT(date_col,'%d/%m/%Y')) as 'concatenated'
FROM mytable;

Demo fiddle