tinyint and leading zero

[webgodjj]

Hi,

I have a table that I hold the month and day of a person's birthday. I declared these columns as a tinyint. However, tinyint seems to drop the leading zero:

07 becomes 7

Is there a way around this or should I declare my column differently?

[jetboy]

Not used it myself, but take a look at the ZEROFILL attribute on the MySQL site - that should do what you need.

[r937]

this is actually a fairly deep topic

an integer is not stored as decimal digits with or without leading zeroes, it is stored as a series of 0 and 1 bits (binary digits)

it is converted on the way in, and again on the way out

leading zeroes are visible, or not, depending on how you choose to display or convert the number

if you do not format the number, then, by default, it does not show leading zeroes

so if you want to show a leading zero, use something like RIGHT('0'+mth,2) which converts and concatenates the month number onto a character '0' string and then takes the last two characters

so that answers the first part of your original question (is there a way around this)

the second part (should I declare my column differently) is also interesting

rather than storing separate month and day fields, i would store the person's actual birthdate in a DATE field

but if that's not possible (for example, on some forums, you can enter your birthday month and day, but are not required to enter your birth year) then two tinyints will do nicely, but then, so will two CHAR(2) fields, and then you can enter the leading zeroes into those fields

rudy

[webgodjj]

rather than storing separate month and day fields, i would store the person's actual birthdate in a DATE field

That would be great, however, I'm unsure how to combine the numbers correctly for this...

The numbers I have are birth_month, birth_day, and birth_year

Cheers,

JJ

[r937]

you have all three? great

backup or make a copy of your table first, then run these --

ALTER TABLE yourtable
ADD COLUMN birthdate DATE

UPDATE yourtable
SET birthdate = birth_year*10000 + birth_month*100 + birth_day

ALTER TABLE yourtable
DROP COLUMN birth_year

ALTER TABLE yourtable
DROP COLUMN birth_month

ALTER TABLE yourtable
DROP COLUMN birth_day

now you are poised to take advantage of all the great date arithmetic functions that mysql offers --
http://www.mysql.com/doc/en/Date_and...functions.html