查看mysql最新日期格式,MySQL查询-在输出格式日期？

最新推荐文章于 2022-04-04 21:34:20 发布

uare大猪蹄子

最新推荐文章于 2022-04-04 21:34:20 发布

阅读量64

点赞数

文章标签：查看mysql最新日期格式

In my table, dates are stored like this: 2011-03-03T13:30:00

I'm trying to output dates like this: March 3, 2011 1:30 PM

I'd much rather work it into the query rather than use php to format it, but I'm having some difficulty doing that. Trying various iterations of DATE_FORMAT, but it's not giving me what I want, maybe because of the way it's being stored?

解决方案

You basically have two different operations you may need to perform when handling dates: date to string and vice versa. The functions you can use are DATE_FORMAT() and STR_TO_DATE(). Full reference can be found in the manual.

Usage example:

SELECT

DATE_FORMAT(CURRENT_TIMESTAMP, '%d/%m/%Y %H:%i:%s'),

STR_TO_DATE('31/12/2001 23:55:00', '%d/%m/%Y %H:%i:%s')

If your dates are not real dates but strings, you'll need to convert twice: from string to date and again from date to string:

SELECT

STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'),

DATE_FORMAT(STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'), '%M %e, %Y %l:%i %p')