Page 1 of 1

MySQL query concat problem

Posted: Tue Jan 30, 2007 8:11 pm
by abshaw
I have php and mysql running, the database has a table named torders and there are 3 fields that store the date (not my fault, was handed to me like this-i know better than this)

Field Name------------Sample data
date--------------------7
month------------------November
year--------------------2006

all i am trying to do is write an sql query to

1. join / contatinate all these fields to make a string date
2. convert the string date to a real date
3. format the date to display as Tuesday, November 7, 2006
4. Sort order by this date as well.

here is what i have done so far and i am stratching my head up and down.

$query1 = "Select str_to_date(day-month-year, '%d-%b-%Y') as modate from torders order by modate";
second try was
$query1 = "Select str_to_date(CONCAT(day,'-', month,'-', year), '%d-%b-%Y') as modate from torders";
then i gave up and did not try the //DATE_FORMAT(, '%d/%M/%Y')"; at all.

can any one of you great mainds help

Posted: Tue Jan 30, 2007 8:41 pm
by feyd
Why not write a query (or series of them) to shift the separate fields into a single date field?

Posted: Wed Jan 31, 2007 2:28 am
by abshaw
how do i do that?

Posted: Wed Jan 31, 2007 7:59 am
by feyd
First you have to add a column to the table that is a DATE type. Second, you figure out an UPDATE query that can combine the three separate fields and assign that result to the DATE field. Finally, you remove the three separate columns.

Posted: Wed Jan 31, 2007 6:42 pm
by RobertGonzalez
Moved to Databases.

Posted: Thu Feb 01, 2007 12:25 am
by califdon
feyd wrote:First you have to add a column to the table that is a DATE type. Second, you figure out an UPDATE query that can combine the three separate fields and assign that result to the DATE field. Finally, you remove the three separate columns.
That's correct, of course, but I'm afraid the original question was how to combine the three separate fields and assign that result to the Date field. :? Converting the day and year are obvious, but you may have to populate a hash array with the month names and their equivalent numbers, to convert the month. Once you have all 3 as numbers, the conversion to date format should be quite easy.