4

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');

I try to run this query:

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

But it always return rows with null values.

If I run the query:

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

It will return 2014-12-25

So what wrong with my query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Thinh Phan
  • 95
  • 1
  • 1
  • 8

2 Answers2

7

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

SELECT DATE(birthday) FROM `test`

Result:

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

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.

Example:

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
scrowler
  • 24,273
  • 9
  • 60
  • 92
2

You have COLLATE problem with your creation of table. If you use utf8_unicode_ci then there is no any problem. See this example on sqlfiddle. More about collation you can read this Post.

Community
  • 1
  • 1
Sadikhasan
  • 18,365
  • 21
  • 80
  • 122
  • +1 for this, but it will still return null for values that don't match the format (e.g. YYYY-MM-DD as in row 2). It also works fine if you don't specify a COLLATE at all. – scrowler Aug 29 '14 at 03:41
  • @scrowler You are right `if you don't specify a COLLATE` then it will take default utf8_unicode_ci that's why it work. – Sadikhasan Aug 29 '14 at 03:48
  • It's still not the right solution for this problem, as the inconsistency of the input data format means it's the wrong function to choose. – scrowler Aug 29 '14 at 03:58