MySQL Concat problem with Date format

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

[chronister]

I may be wrong here, but I think that you would have to pull the values out and use php to join the pieces together and run the query again with the new date string... but like I said I may be wrong

try the DATE_FORMAT(, '%d/%M/%Y') option, that may be the ticket


You could also add a new field to the table and make it a datetime field that auto inserts the current datetime then use that to sort by

[bqallover]

This query should work, had to double up on the str_to_date function as it wouldn't recognize 'modate' as a column in the select... fair enough!...

SELECT STR_TO_DATE(CONCAT(date,' ',month,' ',year), '%e %M %Y') as modate, DATE_FORMAT( STR_TO_DATE(CONCAT(date,' ',month,' ',year), '%e %M %Y'), '%W, %M %e, %Y') as datestring FROM torder ORDER BY modate;

...will give you something like this...
[pre]
+------------+---------------------------+
| modate    | datestring                |
+------------+---------------------------+
| 2004-04-01 | Thursday, April 1, 2004  |
| 2006-11-07 | Tuesday, November 7, 2006 |
+------------+---------------------------+
2 rows in set (0.00 sec)[/pre]

Phew!  That was a slog. :)

[Sir William]

Since your date info is likely to be an issue down the road, I'd write a small script to pull the three fields, join them to create a proper date string then put that string in a new datetime field in the table.  You will still have your old values for whatever legacy script may need them, but you'll also have a proper datetime value that you can use as you write new code or rewrite the original stuff.

Just my $.02.

[bqallover]

sir william is right, you should add a new field, but the above script will work.  ;D

[Sir William]

What bqallover has will work, but my thought was that having to add in that code to every future query will make things that much harder to read as you develop more code.  Not trying to detract from cleverly structured SQL.  :D  On the contrary, the better I get at building effective queries, the less PHP I have to write to do what I want.

[abshaw]

thank you for the reply bqallover, and sir william, I have tried the given query and it doesnt work. On the next go around i broke the query down and it works in steps, here is what i got.

STEP 1.
$query1 = "SELECT CONCAT(day,' ',month,' ',year) as modate FROM torders ORDER BY modate";

works and here are the first 3 lines of result
1 April 2006
1 January 2007
1 October 2006
10 December 2006

STEP 2.
$query1 = "SELECT STR_TO_DATE(CONCAT(day,' ',month,' ',year), '%e %M %Y') as modate FROM torders ORDER BY modate";
does not work and here is the error i get

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(CONCAT(day,' ',month,' ',year), '%e %M %Y') as modate FROM tor

after getting this error i did a phpinfo() on the server and here is what i got
MySQL-------------------------------- Support enabled
Active Persistent Links-----------------  0 
Active Links---------------------------  0 
Client API version----------------------  4.0.16 
MYSQL_MODULE_TYPE-----------------  external 
MYSQL_SOCKET-----------------------  /tmp/mysql.sock 
MYSQL_INCLUDE-----------------------  -I/usr/local/mysql/include/mysql 
MYSQL_LIBS -------------------------- -L/usr/local/mysql/lib/mysql -lmysqlclient 

and this is all on a FreeBSD ----4.11-STABLE FreeBSD 4.11-STABLE #0: Wed Apr i386


what am i doing wrong, please help

[artacus]

I'm not sure but my hunch is to try putting quotes in
CONCAT('"', day, ...)

[abshaw]

Ok tried the quotes as well still get this error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('CONCAT(day,' ',month,' ',year)', '%e %M %Y') as modate FROM t

[fenway]

I assume you have a high enough MySQL version (4.1.1+)?

[bqallover]

Strange.  I've ran this again, using the query that didn't work for you.  The only difference is the table name (missing an 's') and I named the field 'date' instead of 'day', as in the field definitions in your first post.  I've included some version information to help, but it worked for me.

[pre]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.11-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use datetest
Database changed

mysql> show create table torder;

  ... snip ...

CREATE TABLE `torder` (
  `date` int(11) default NULL,
  `month` varchar(11) default NULL,
  `year` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

  ... snip ...

1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE(CONCAT(date,' ',month,' ',year), '%e %M %Y') as modate FROM torder ORDER BY modate;
+------------+
| modate    |
+------------+
| 2004-04-01 |
| 2006-11-07 |
+------------+
2 rows in set (0.00 sec)
[/pre]

Are your field types the same as the ones I've used?  It's a long shot, but apart from having an old server version, which you don't seem to have, that's all I can think of.

[edit] Misread your FreeBSD version as MySQL version.  As fenway suggests, that may be the problem [/edit]