Page 1 of 1

Making month MONTH() return zero padded values

Posted: Mon Apr 30, 2007 12:16 pm
by GeertDD
Is there a simple way to make the MONTH() function return zero padded values, so 01-12 instead of 1-12? I would like to handle this in the query and not by iterating through the result just to sprintf() it.

Code: Select all

SELECT MONTH(date_field) FROM table

Update: some creative thinking has lead me to the following query. It works. However, I feel like this should be done in a more straightforward way.

Code: Select all

SELECT SUBSTRING(CONCAT('0', MONTH(date_field)) FROM -2 FOR 2) FROM table

Posted: Mon Apr 30, 2007 4:55 pm
by RobertGonzalez
DATE_FORMAT()

Code: Select all

SELECT DATE_FORMAT(date_field, '%m') FROM `mytable`;

Posted: Tue May 01, 2007 3:02 am
by GeertDD
Of course! Why didn't I think of that. So simple. :oops:

Thanks, Everah.

Posted: Tue May 01, 2007 10:39 am
by RobertGonzalez
You got it.