mysql month with leading zero code example
Example 1: mysql format number leading zeros
Here’s an example of padding a single digit number with two zeros:
SELECT LPAD(7, 3, 0);
Result:
+
| LPAD(7, 3, 0) |
+
| 007 |
+
In this case, two leading zeros were added because we specified 3 as the required length.
So if we start with a two digit number, only one zero is added:
SELECT LPAD(17, 3, 0);
Result:
+
| LPAD(17, 3, 0) |
+
| 017 |
+
Non-Zero Values
The LPAD() function isn’t limited to just zeros. As mentioned, it can be used to pad any string with any other string. So you can pad a number with leading 1s, or leading letters, or other symbols if required.
SELECT LPAD(7, 10, '.');
Result:
+
| LPAD(7, 10, '.') |
+
| .........7 |
+
And because it’s actually a string function, it can be used to pad any non-numeric string. And it’s not limited to just one padding character – it can be padded with multiple characters if need be:
SELECT LPAD('Cat', 21, 'Meow! ') AS Result;
Result:
+
| Result |
+
| Meow! Meow! Meow! Cat |
+
Example 2: mysql extract day from date leading zero
DATE_FORMAT(field_name,'%Y-%m-%d')