Web Analytics Made Easy -
StatCounter Storing dates - CodingForum


No announcement yet.

Storing dates

  • Filter
  • Time
  • Show
Clear All
new posts

  • Storing dates

    I'm currently in the process of migrating from MS Access to MySQL. In my MS Access database the dates are stored in the format MM/DD/YYYY. However on MySQL it seems to want the format YYYY/MM/DD. Therefore the dates aren't being stored properly when I'm importing data into my MySQL database, they are just being displayed as 0000-00-00. Is there a way I can change the format the dates are saved in MySQL?


  • #2
    here is how I would do the import. make the format of your date column varchar/char FOR THE IMPORT ONLY (if you leave it that way then you will not have any DATE/TIME functionality on your column).

    using varchar/char will allow you to import your dates as is. then create a new column in your table with DATE as the column type, and make the change to your values to put them in yyyymmdd format to place them in that column. you then delete the original date column that was char/varchar format.

    you want DATE as the type because you can use date and time functions on the column and you can then use DATE_FORMAT to display the dates in any number of ways.

    See the article I have linked below on how to make the date switch from char/varchar back to date, it is a simple process if you are running 4.1 and you'd only need to do it once. If you are on older system then the procedure is in that article too, it is just a bit more involved.


    • #3
      Thanks guelphdad. I was worried I may have to enter all the data in manually which would be a nightmare.