CountryGirl Posted February 1, 2010 Share Posted February 1, 2010 In several of the fields in my tables there are one too many 0's after the number. Like, say, there is a table that has a "YearBuilt" field in it. The year built is there, but after the year built, there are a bunch of extra zeros. Like this "1886.00000." Of course it comes through in the search results and obviously the company I'm working for doesn't like all the extra zeros. In the original microsoft access DB there are not extra zeros in those fields. It's just there in the PHPMyAdmin tables. How do I fix that and get rid of the extra zeros? Thanks! Qadoshyah Quote Link to comment Share on other sites More sharing options...
schilly Posted February 1, 2010 Share Posted February 1, 2010 what type is that field? Quote Link to comment Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 what type is that field? "Type: double(15,5) Null: Yes Default: NULL" That's what it says. Thanks! Qadoshyah Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted February 1, 2010 Share Posted February 1, 2010 Set your field type to double(15,2). Now numbers will only have two decimal places, eg 123.45 Quote Link to comment Share on other sites More sharing options...
schilly Posted February 1, 2010 Share Posted February 1, 2010 well if it's a year you probably want no zeros so (15,0) Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted February 1, 2010 Share Posted February 1, 2010 well if it's a year you probably want no zeros so (15,0) Misread the OP. If its a year then why use double,? Why not just use INT if you're storing years. Quote Link to comment Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 Thank you guys! That works . I'm getting all the tables fixed now! Qadoshyah Quote Link to comment Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 well if it's a year you probably want no zeros so (15,0) Misread the OP. If its a year then why use double,? Why not just use INT if you're storing years. I'm not sure. I wasn't the one who loaded the information into the PHPMyAdmin. It seems to be working though now with the double 15,0. I may see if INT works better though. Thanks! Qadoshyah Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 1, 2010 Share Posted February 1, 2010 Even better, MySQL has a YEAR(4) type. The only caveat is that the year has to be in the range of 1901 to 2155. Why should you care? With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte! Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size. Quote Link to comment Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 Even better, MySQL has a YEAR(4) type. The only caveat is that the year has to be in the range of 1901 to 2155. Why should you care? With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte! Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size. Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates. Qadoshyah Quote Link to comment Share on other sites More sharing options...
gizmola Posted February 1, 2010 Share Posted February 1, 2010 Even better, MySQL has a YEAR(4) type. The only caveat is that the year has to be in the range of 1901 to 2155. Why should you care? With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte! Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size. Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates. Qadoshyah If that is the case, then I'd recommend you use an unsigned smallint. Only requires 2 bytes, and you will not have any issue with range, as it allows values up to 64k. Quote Link to comment Share on other sites More sharing options...
CountryGirl Posted February 1, 2010 Author Share Posted February 1, 2010 Even better, MySQL has a YEAR(4) type. The only caveat is that the year has to be in the range of 1901 to 2155. Why should you care? With the myisam engine, a double column requires 8 bytes, whereas a year only requires 1 byte! Saving 7 bytes per row for both data and index if needed, is always extremely important to consider for a database of any significant size. Thanks! I tried using smallint and it doesn't work for the date column I tried it on. The date column I tried it on has to have the date like "1994-10-11." When I do smallint, it only gives the year. No day & month. Qadoshyah Thanks for the info! I will keep that in mind. Unfortunately, I have a decent amount of dates that go back before 1901. But, I will keep this in mind if I can use it for any dates. Qadoshyah If that is the case, then I'd recommend you use an unsigned smallint. Only requires 2 bytes, and you will not have any issue with range, as it allows values up to 64k. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.