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 minds help

    Maybe something like this will work:

    SELECT DATE_FORMAT(STR_TO_DATE(CONCAT(date, '-', month,'-', year), '%e-%M-%Y') AS mydate, '%W, %b &e, %Y') AS modate
           FROM torders ORDER BY mydate;

    It shouldn't be too hard to put those date fragments into a sane date type column, and not have to go through these gymnastics with every new query.

      5 days later

      The easiest way would be to just add a column and update it with the correct date from the other columns. You would not even have to use str_to_date as mysql will accept a string like 2007-2-5 and convert it itself. I suppose there is a form with seperate inputs for day. month and year and the script just stores them, so amend that to store the correct date in future

        I'll second Roger's suggestion. Even if you don't have a development server running to make these changes on, you could practically upgrade the structure while the DB is running and then switch your code over to use the new column. Once you've switched the code, then you simply drop the three separated columns and you're done.

          Write a Reply...