mysql date

[Anon]

What I was wondering is if there is an automatic way to convert the dates I have in my mysql database into a format mysql column type "Date" will support.

My current column type is varchar which is a column with dates in the format of 00/00/0000 or 0/0/0000 depending on the year or day. I believe mysql date column will only accept 0000/00/00. The reason I need to change the column type to date is because I think the "between" function will not work unless the column type is date.

thanks

[[deleted]]

It is never good to store the date as a varchar.

Instead, store it as a DATE type, which means converting the date to 0000/00/00 before you input it, or change the type to INTEGER and store a timestamp.

[Anon]

I have over 800 records in this table do I have to manually change the data to YYYY/MM/DD,  or does someone have a script that will change all my data from MM/DD/YYYY to YYYY/MM/DD.

thanks

joe

[[deleted]]

A script? lol changing thaqt format is one single SQL statement. Read the mysql manual about the string functions.

[frigidman]

Here is something that might allow you to convert all your VARCHAR formatted date fields to the DATE field for re-insert:

[this is untested, be careful, its just an example]

Do a SQL to select rows of data based on if the date has been converted yet or not. Then loop through the results with a while() loop and store into vars using the "list()" function:
while ($row = mysql_fetch_array($sql_statement)) {
list($rowidentifier,$olddate) = $row;

Then, take the field that is the VARCHAR date and do

#(assuming your date uses / to seperate items):
$splitup = explode('/',$olddate);

#Then remake a date to insert into mysql like so
#(assuming your date was once MM/DD/YYYY):
$newdate = "$splitup[2]-$splitup[0]-$splitup[1]";

#This will put the year up front with month and day following.
#Then you can take "$newdate" and put it in the SQL query to
#update your data, or insert a new record
#(whatever you need to do with it).
}

Be careful though, as if the script times out due to too many entries to loop through, you have to figure out where to start again. One way would just add some logic to your initial "grab data" sql to say "if the newdate field is empty, then grab those rows to work on"... because obviously if its empty, it means it hasnt gotten to it yet. Right?

I hope that all makes sense... I try to write the stuff out in simple english as its much easier than spewing out terms to you when you might actually know what they are (I get in that same boat myself).

[[deleted]]

First, I would forget about doing this in a script.
Do it in the commandline tool, that's what it is there for.

Second, the script will not time out if you use set_time_limit() to reset the limit.

Third, you can select the converted date like this:

SELECT CONCAT(SUBSTRING(datefield, 7, 4), '/', SUBSTRING(datefield, 4, 2), '/', SUBSTRING(datefield, 1, 2)) FROM table;

I probably switched the DD and MM around, check it.

To update all the records in one go:
UPDATE table
SET datefield = CONCAT(SUBSTRING(datefield, 7, 4), '/', SUBSTRING(datefield, 4, 2), '/', SUBSTRING(datefield, 1, 2));

I STRONGLY advise that you TEST this thoroughly before you do it on the final data, and make sure you have a backup of that data before you do it.

After the conversion is complete you can change the column type from varchar/char to date, which will automatically convert the strings to 'real' dates.