MySQL的STR_TO_DATE不工作 [英] mysql STR_TO_DATE not working

问题描述

我有一个表，其中有一个名为birthday

I have a table with a varchar column called birthday

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `birthday` varchar(30) COLLATE utf16_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf16 COLLATE=utf16_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO `test` (`id`, `birthday`) 
VALUES (1, '20041225'),
       (2, '2004-12-25'),
       (3, '19941225'),
       (4, '19941201');

我尝试运行此查询:

SELECT str_to_date(birthday,"%Y%m%d") 
FROM `test` 
WHERE 1

但是它总是返回带有空值的行.

But it always return rows with null values.

如果我运行查询:

SELECT str_to_date('20141225',"%Y%m%d") 
FROM `test` 
WHERE 1

它将返回2014-12-25

那我的查询怎么了?

推荐答案

将其保留到

Leave it to simpler functions. DATE() returns the date part of a string in YYYY-MM-DD format:

SELECT DATE(birthday) FROM `test`

结果:

2004-12-25      
2004-12-25      
1994-12-25      
1994-12-01      

---

您的代码无法正常运行的原因是STR_TO_DATE()需要相同的输入和输出格式，例如STR_TO_DATE('2014-08-29', '%Y-%m-%d').看看文档中的示例.此函数通常用于将日期或时间从一种格式转换为另一种格式，其中原始格式是MySQL外部的某种格式，并且您想将数据导入MySQL的日期格式，例如，在这种情况下，您会知道原始格式是什么日期格式为

---

The reason your code isn't working is that STR_TO_DATE() expects the same input and output formats, e.g. STR_TO_DATE('2014-08-29', '%Y-%m-%d'). Take a look at the examples in the documentation. This function is used mostly to convert dates or times from one format to another, where the original format is something from outside MySQL and you want to import the data into MySQL's date format for example - in this case, you'll know what the original date format is.

示例:

SELECT STR_TO_DATE('20041225', '%Y-%m-$d');   -- null - formats don't match
SELECT STR_TO_DATE('2004-12-25', '%Y-%m-%d'); -- 2004-12-25 - formats match

这篇关于MySQL的STR_TO_DATE不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！