mysql - 连接日期函数mysql

Question

mysql 查询意外返回。我正在尝试连接一些包含日期查询的字符串，如下所示:

mysql> select month(timestamp) +'-'+ day(timestamp) + '-' + 
       year(timestamp) as date FROM table ORDER BY timestamp DESC LIMIT 1;

我得到一个 4 位数的结果，我认为这是预期结果的总和:

+------+
| date |
+------+
| 2035 |
+------+

我也试过将每一个都转换为 CHAR 但没有用:

mysql> select CAST(month(timestamp) as CHAR) +'-'+ CAST(day(timestamp) as CHAR) + '-' + 
CAST(year(timestamp) as CHAR) as date FROM table ORDER BY timestamp DESC LIMIT 1;

有人可以让我知道我做错了什么吗？ ...我想如何修复它也很好 :)

Answer 1

在 MySQL 中，使用 CONCAT_WS

CONCAT_WS('-', month(timestamp),day(timestamp),year(timestamp))

查询，

SELECT CONCAT_WS('-', month(timestamp),day(timestamp),year(timestamp)) AS DATE
FROM   tablename
ORDER  BY timestamp DESC 
LIMIT  1

或使用DATE_FORMAT

SELECT DATE_FORMAT(timestamp, '%m-%d-%Y') AS DATE
FROM   Table1
ORDER  BY timestamp DESC 
LIMIT  1

• SQLFiddle Demo

其他:

• DATE_Format
• CONCAT_WS