MySQL a little help
Hi, my knowledge of mysql is limited to 'SELECT * FROM TABLE; ' :)
I have this table with a field (pur_date) as date and i need to copy it to another field in the same table (field4) as varchar(10) with the format 'mm/dd/yyyy' somebody could help me?
If you mean how to do that in one mysql command, I'm not sure. But if you can do it in two separate calls, via the shell, then you can try something like this:
Note: the part of the mysql commands WHERE field1='value' can be left off, it is just a place-holder for you to insert your own way of uniquely identifying the record.
# define connection parameters
# mysql command to reuse
mysql_cmd="mysql -sN -u $USERNAME -p$PASSWORD $DATABASE"
# run command to get the date
date=$($mysql_cmd -e 'SELECT pur_date FROM $DB_TABLE WHERE field1="value"')
echo date: $date
# date will look something like '2012-12-13'
# now separate month, day, and year
MM=$(echo $date|cut -f2 -d-)
DD=$(echo $date|cut -f3 -d-)
YY=$(echo $date|cut -f1 -d-)
# put them together in desired format (MM/DD/YYYY)
echo date: $date
# run mysql command to insert the new date value into the db
$mysql_cmd -e "UPDATE $DB_TABLE SET field4='$newdate' WHERE field1='value'"
Originally Posted by atreyu
And how it would be to run it for the whole table and not just a certain record?
oh, if you were doing the whole table then I guess you could just leave off the "WHERE field=value" bit.
Originally Posted by arespi
Be careful: if setting the new date was a per-row action (i.e. a different value for every row) the above script wouldn't work. The following SQL (or a slightly modified version of it) could instead do the job:
The update command is executed for every row matched. Since there is no WHERE it executes on every row and passes the value of datefield into DATE_FORMAT and writes the outcome into the field column of the same row.
UPDATE table SET field=DATE_FORMAT(datefield,'%m/%d/%y')
RTFM: MySQL :: MySQL 5.5 Reference Manual :: 12.7 Date and Time Functions
Thanks a million Kloshüssel,that did the trick!. Just had to change the %y for %Y because it need a 4 digit year.