MySQL partial date in Datetime column

Question

i have some partial dates

like 2017-12 (Y-M) or 2017-45 (Y-W)

is it possible to store it in a datetime column in MySQL ?

Possible duplicate of [How to convert DateTime to VarChar](http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar) — Kahn Kah, Apr 14 '17 at 09:40
http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar — Kahn Kah, Apr 14 '17 at 09:40
Adding the first day of the month or the first day of the week — etsa, Apr 14 '17 at 09:43
You can store 201712 adopting the convention that in that case you store it as 20171201. In a similar manner, 2017W45 can be converted in 2017xxyy, using the first day of that week. I suppose it's better f all the rows adopt the same convention in the same column. Why do you want to store them in a datetime column? If you give us more details some one could help you in a better way. — etsa, Apr 14 '17 at 09:53

score 0 · Accepted Answer · answered Apr 14 '17 at 10:09

use STR_TO_DATE() function like this:

SELECT STR_TO_DATE(CONCAT('2017-12','-01'),'%Y-%m-%d');


SELECT STR_TO_DATE(CONCAT(SUBSTRING_INDEX('2017-45', '-', 1),'-01-01'),'%Y-%m-%d') 
       + INTERVAL SUBSTRING_INDEX('2017-45', '-', -1) -1 WEEK;

sample

mysql>     SELECT STR_TO_DATE(CONCAT('2017-12','-01'),'%Y-%m-%d');
+-------------------------------------------------+
| STR_TO_DATE(CONCAT('2017-12','-01'),'%Y-%m-%d') |
+-------------------------------------------------+
| 2017-12-01                                      |
+-------------------------------------------------+
1 row in set (0,00 sec)

mysql>     SELECT STR_TO_DATE(CONCAT(SUBSTRING_INDEX('2017-45', '-', 1),'-01-01'),'%Y-%m-%d')
    ->            + INTERVAL SUBSTRING_INDEX('2017-45', '-', -1) -1 WEEK;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| STR_TO_DATE(CONCAT(SUBSTRING_INDEX('2017-45', '-', 1),'-01-01'),'%Y-%m-%d')
           + INTERVAL SUBSTRING_INDEX('2017-45', '-', -1) -1 WEEK |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| 2017-11-05                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)

mysql>

MySQL partial date in Datetime column

1 Answers1