在MySQL中转换日期 [英] Converting dates in MySQL

问题描述

我在MySQL数据库中有一列，其日期格式为:

I have a column in a MySQL database that has dates in the form:

Tue Oct 25 2016.我正在尝试以10/25/2016的形式获取它.

Tue Oct 25 2016. I am trying to get it in the form 10/25/2016.

我做了一些研究并尝试过:

I did some research and tried this:

SELECT DATE_FORMAT(Date, '%d/%m/%Y') FROM table;

但是它返回null

任何帮助将不胜感激.

推荐答案

首先，您需要将日期 string 转换为

Firstly, you will need to convert your date string to MySQL date format ('YYYY-MM-DD'), using STR_TO_DATE function. To convert from string, we have to specify the current format of the date string. In your case, it is '%a %b %d %Y'. Note that the % character is required before format specifier characters.

详细信息:

• %a缩写的工作日名称(星期日至星期六)
• %b月份的缩写(1月至12月)
• %d每月的一天，以数字值(01到31)
• %Y以4位数字表示的年份

• %a Abbreviated weekday name (Sun to Sat)
• %b Abbreviated month name (Jan to Dec)
• %d Day of the month as a numeric value (01 to 31)
• %Y Year as a numeric, 4-digit value

现在，您可以使用 DATE_FORMAT 函数将MySQL日期转换为所需的日期日期字符串格式.在您的情况下，它将是:'％m/％d/％Y'

Now, you can utilize DATE_FORMAT function to convert the MySQL date into the desired date string format. In your case, it will be: '%m/%d/%Y'

详细信息:

• %d每月的一天，以数字值(01到31)
• %m月名称作为数字值(00到12)
• %Y以4位数字表示的年份

• %d Day of the month as a numeric value (01 to 31)
• %m Month name as a numeric value (00 to 12)
• %Y Year as a numeric, 4-digit value

尝试以下查询:

SELECT DATE_FORMAT(STR_TO_DATE(Date, '%a %b %d %Y'), '%m/%d/%Y') 
FROM table;

可用格式说明符的完整列表可以在以下位置查看:

Complete list of available format specifiers can be seen at: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

这篇关于在MySQL中转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！