Making month MONTH() return zero padded values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Making month MONTH() return zero padded values

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

DATE_FORMAT()

Code: Select all

SELECT DATE_FORMAT(date_field, '%m') FROM `mytable`;
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Post by GeertDD »

Of course! Why didn't I think of that. So simple. :oops:

Thanks, Everah.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You got it.
Post Reply