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 ....
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 :)
you can use this:
SELECT STR_TO_DATE(yourdatefield, '%m/%d/%Y') as
FROM table
'yourdatefield' replace with your date column name.
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)]>
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') ;
