How to convert date format's in MySQL.
DatabasesAs you can see in my Blog entry on how to import a Microsoft Access Database to MySQL , I ran into a small problem on converting all the dates from the format of mm/dd/yyyy to MySQL Date Format of yyyy-mm-dd.
Thanks to all the help I recieved from Guelphad on the MySQL Newbie forum in this post, I got all the dates converted and am good shape.
Here's how I converted the date formats on Guedlphad's Advice.
In my table(misequip) , I created a new column with a data type of DATE with a name (dt_deployed) similar to the column that contained my dates imported from Access (date_deployed) in string format. If you'll remember, I imported all the dates as a datatype of VARCHAR just to get them into the database.
I then ran this command:
mysql> UPDATE misequip SET dt_deployed = str_to_date(date_deployed, '%m/%d/%Y');
What that command does is UPDATE's the misequip table, SET's the new column dt_deployed to equal the out put of the MySQL function STR_TO_DATE that converts Strings to Dates.
Once I ran this command, I looked at the my database and all the dates were converted. Worked like a champ. I then dropped my original column, then renamed my new column to match the old name, date_deployed.
We will now be albe to do calculations on these dates now since they are in DATE format to figure out when warranties expire and stuff like that.
Hope this help some of you newbieMySQL guys out there,
Mark
How to import Microsoft Access database into MySql
DatabasesI'm working on web enabling my inventory of IT Equipment with ColdFusion and MySql. The database is an Access database that is pretty simple, just name, model number, date of purchase, blah, blah, blah.
The first thing I've had to do to get this accomplished is to export my Access database as a tab delimited text file. In Access, under the File menu, I went to Export. That opened the Export Wizard. I gave the file a name "inv.tab", then on the first screen of that Wizard I choose delimited, on the second screen of the wizard choose "Tab", then set the "Text Qualifier" option to "None". Set a location for the exported file to reside, then clicked the Finish button. Upon confirmation of the successful export, I opened the text file I created with Access with a text editor (gEdit in Linux, Notepad in Windows) and looked at the file to make sure that all the columns and data looked ok. I've got 3 date columns in the database, and noticed that behind each date was a time and that the date format was wrong and wouldn't work in MySql. (We'll tackle the date problem in another post as I haven't figure out how to convert them all to the proper date format for MySql without touching each date by hand) Anyway, I did a find and replace and replaced all the times in the file with a space. This application I only need the dates. I verified that all the times were gone, then saved the file.
Then I connected to MySql via the command line client and create my database and created my tables to include all the columns to match the columns in my Access database. On the three date fields, I set them up with data types of VARCHAR(20). For now as I'm just wanting to get the data in the database. I also made all the columns to allow NULL's except the Primary Key. I'm working with some people on the MySql newbie forum to get help on converting the dates to the correct format. There is a MySql function STR_TO_DATE that will convert strings to dates in the correct format. I'm hoping that will help me convert all the dates at once.
Once the table was created, and the export files was checked, I started my first import by using the LOAD command in MySql.
I copied one record from the export file (inv.tab) into a new file (test.tab) and ran a test on it to make sure it would import the file in the correct columns.
Then, in the MySQL Command line client, I imported my test file (test.tab) with this command
mysql>LOAD DATA LOCAL INFILE '/home/mbrown/test.tab' INTO TABLE misequip;
Much to my amazement it ran without error.
so I did a mysql>SELECT * FROM misequip;
Sure enough, there was my data. Cool. So, now I tried the big file.
I opened the inv.tab export file, made sure the line I already imported was gone, then type this:
mysql>LOAD DATA LOCAL INFILE '/home/mbrown/inv.tab' INTO TABLE misequip;
No errors again. Cool!
Then I did a mysql>SELECT * from misequip; and BOOM, there's all my data! SWEEETTTT! I love MySql! LOL
Now I'm going to tackle the date convert thing. I'll keep you posted.
First Experiences with MySql
Databases
I've been playing with MySql a bit lately and have to say I'm impressed. To me this is a product of the Open Source world that really shows the benefits of Open Source software, especially in the documentation.
I've really just been playing with it, so I don't have any highly technical revelations to report yet, but what really has impressed me so far is the documentation that comes with it in the help file. You can find it on there web site here . For a noob like me the tutorial in that documentation is invaluable. Very simple, easy step by step examples on creating your first database, table, and getting data into and out of it.
Aaron Lynch , Dave Shuck and Rob Wilkerson all encouraged me to check it out and initially I'm glad I took their advise. Thanks fella's.
Database preferences and opinions.
DatabasesSo, this is probably one of those loaded questions like asking if a group of Nascar Fans likes Ford or Chevy better, but here it comes anyway. Well, after a bit of explanation as to why I'm asking such a question anyway.
Being a newbie to CF development and databases I tend to lean towards simplicity. My background as a IT Manager hasn't gotten me very much experience with the big server based DB's like MSSQL and MySql. In the past, if we've needed "hardcore" db work done by boss tells me to hire a "SQL Guy". So I haven't learned to much about the inner workings of the different server based DB's.
When I started learning ColdFusion I found working with MS Access db's very simple. Easy to setup, easy to edit and easy to muddle thru setting up my first one to many relationships. As far as I can tell Access will do what ever I want or need it to. Hopefully some day I'll have to worry about the limitations of Access, but up until now I was just looking for simplicity. Learning ColdFusion is enough to keep me busy, but I would really like to move over to Ubuntu Full time. (I'll explain why in another yet to come blog entry) But moving to Ubuntu, or any distro of linux makes me look at another db as an option, and of course that leads me to MySql.
I've found a couple of good tutorials and the the documentation on MySql's site is pretty good. It seams easy enough to learn. But I'm curious as to a couple of things:
In your all's opinion....
1. What are the advantages or disadvantages to using MySql for your ColdFusion datasourse?
2. What are the advantages or disadvantages of using MySql over MS Access, MSSql, or any other popular db? Windows or not.
3. It seems like most hosting companies support MySql. Do you find that that support is generally pretty good?
4. For a newbie like me, is this just going to "muddy" the overall learning waters for my new found love of web app. development?
5. Should I just stick with Windows and the initial easy of use and quick configuration to get my budding ColdFusion career going?
Ohh, such deep thoughts and questions...LOL.
Can't wait to read you all's advice.
Later,
Mark
Loading....