SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    tinyint and leading zero

    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?

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not used it myself, but take a look at the ZEROFILL attribute on the MySQL site - that should do what you need.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

  4. #4
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •