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
MySQL query concat problem
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Moved to Databases.
Last edited by RobertGonzalez on Thu Feb 01, 2007 1:19 am, edited 1 time in total.
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.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.