How to convert string to date format in mysql ?

Question

My date format looks like this '201710'. I'm trying to convert this particular string to '2017-10-01'.

How can I convert from the above mentioned format to the required format in mysql ?

I have tried using the below query:

SELECT *, DATE_FORMAT(yymm,'%Y-%m-%d') AS niceDate 
FROM table;

But the niceDate column is showing null.

Can anyone please help me out ....

Possible duplicate of [how to convert a string to date in mysql?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) — Nikos, Jul 04 '18 at 09:38

score 1 · Answer 1 · answered Jul 04 '18 at 09:37

1

I am new to SQL but i think you need to specify the column you want to format, for example im not sure on the exact code for your data but something like;

select *, DATE_FORMAT(yourdata, "%Y/%m/%d") as niceDate
from table;

Change "yourdata" to the date column you wish to format. Let me know if this helps, thanks :)

answered Jul 04 '18 at 09:37

connorg98

189
12

Hi has my solution given you any luck? – connorg98 Jul 04 '18 at 11:18

Zaynul Abadin Tuhin · Answer 2 · 2018-07-04T09:58:25.653

0

You can use STR_TO_DATE() funcion to convert string to date,

In your example you have not provide day part, you provide only year and month but for using this function your string should have year,month and day part Example

SELECT STR_TO_DATE('20171001','%Y%m%d') ;

edited Jul 04 '18 at 09:58

answered Jul 04 '18 at 09:35

Zaynul Abadin Tuhin

31,407
5
33
63

I have tried using STR_TO_DATE() also. But, still the niceDate column is showing null – dev007 Jul 04 '18 at 09:44
you have to provide date part also for this conversion – Zaynul Abadin Tuhin Jul 04 '18 at 09:54
Actually, I'm trying to convert all the values of date field into the required format. Where I'm trying to add like this : SELECT STR_TO_DATE('date','%Y%m%d') from table; where 'date' is a column in my table. – dev007 Jul 04 '18 at 10:50
@dev007 in that by using check date column made it complete year,month and date and then apply this function – Zaynul Abadin Tuhin Jul 04 '18 at 11:30

score 0 · Answer 3 · answered Jul 04 '18 at 09:37

0

you can use this:

  SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y') as 
    FROM    table

'yourdatefield' replace with your date column name.

answered Jul 04 '18 at 09:37

Irfan Nasim

1,952
2
19
29

score 0 · Answer 4 · answered Jul 04 '18 at 09:52

you can CONCAT your string / FIELD then you have a valid DATE

SELECT str_to_date(CONCAT('201710','01'),'%Y%m%d');

SAMPLE

MariaDB [(none)]> SELECT str_to_date(CONCAT('201710','01'),'%Y%m%d');
+---------------------------------------------+
| str_to_date(CONCAT('201710','01'),'%Y%m%d') |
+---------------------------------------------+
| 2017-10-01                                  |
+---------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

How to convert string to date format in mysql ?

4 Answers4