要进行转换,请使用STR_TO_DATE(),如以下语法所示。将日值与CONCAT()-连接select str_to_date(concat('yourDateValue/', yourColumnName), '%d/%m/%y') as anyAliasName
from yourTableName;
让我们创建一个表-mysql> create table demo46
−> (
−> id int not null auto_increment primary key,
−> short_date varchar(20)
−> );
借助insert命令将一些记录插入表中-mysql> insert into demo46(short_date) values('09/18');
mysql> insert into demo46(short_date) values('12/20');
mysql> insert into demo46(short_date) values('11/20');
使用select语句显示表中的记录-mysql> select *from demo46;
这将产生以下输出-+----+------------+
| id | short_date |
+----+------------+
| 1 | 09/18 |
| 2 | 12/20 |
| 3 | 11/20 |
+----+------------+
3 rows in set (0.00 sec)
以下是在MySQL中将MM / YY转换为YYYY-MM-DD的查询。mysql> select str_to_date(concat('24/', short_date), '%d/%m/%y') as Convert_In_Full_Date
−> from demo46;
这将产生以下输出-+----------------------+
| Convert_In_Full_Date |
+----------------------+
| 2018−09−24 |
| 2020−12−24 |
| 2020−11−24 |
+----------------------+
3 rows in set (0.00 sec)