How to add some days to str_to_date mysql function?

Question

I have a table in which i have a string date which was in the format of 01.04.2015. Now I want to add some days to that string date format column. For this I have below query to add some days.

SELECT DATE_ADD(level2,INTERVAL 28 DAY) level2 FROM  sales_purchase_stocks_hs WHERE stock= '123'

When I run this query I am getting wrong date out put. The out put is 2001-05-18 15:00:00. But my original date string was 01.04.2015 (April 1st 2015).

I tried with the below query

SELECT STR_TO_DATE(DATE_ADD(level2,INTERVAL 28 DAY),'%d.%m.%Y') level2 FROM  sales_purchase_stocks_hs WHERE stock = '123'.

But when I run the query I am getting null as result. Can any one please help me where I am doing wrong?

Answer 1

You are doing it wrong. You need to first convert the string date to real date using str_to_date and then apply date_add() on it.

Here how str_to_date works

mysql> select str_to_date('01.04.2015','%d.%m.%Y') as date;
+------------+
| date       |
+------------+
| 2015-04-01 |
+------------+
1 row in set (0.00 sec)

Now use the above inside the date_add() so you have

mysql> select date_add(str_to_date('01.04.2015','%d.%m.%Y'),interval 28 day) as date;
+------------+
| date       |
+------------+
| 2015-04-29 |
+------------+
1 row in set (0.00 sec)

You now can use the above in the query.