Quote:
Originally Posted by BradM
Keith,
10 rows of
-02-
|
What version of mysql are you using ? I don't see why the first/last substrings wouldn't work unless it's a version thing.
I made a test case for oyu actually and on my server it works perfectly:
mysql> desc datefix;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| date1 | varchar(10) | YES | | NULL | |
| date2 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from datefix;
+------------+-------+
| date1 | date2 |
+------------+-------+
| 03-23-2007 | NULL |
+------------+-------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(SUBSTRING(date1,-4),'-',SUBSTRING(date1,1,2),'-',SUBSTRING(date1 FROM -7 FOR 2)) from datefix;
+-----------------------------------------------------------------------------------------+
| CONCAT(SUBSTRING(date1,-4),'-',SUBSTRING(date1,1,2),'-',SUBSTRING(date1 FROM -7 FOR 2)) |
+-----------------------------------------------------------------------------------------+
| 2007-03-23 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)