date interval in sql coding

Question

I have a table like below:

Creationdate         Priority             interval
10/10/2020             P0                    1
7/10/2020              P1                    7

I want to add the interval to each date. for example if the priority is p0 then creationdate+1,

The problem is the date is stored as string. How can I get the result?

Date functions are notoriously vendor-specific. Please tag your question with the database you are running: mysql, oracle, sqlserver...? — GMB, Oct 02 '20 at 16:38

Dark Knight · Answer 1 · 2020-10-02T16:55:02.757

2

First convert string to date using STR_TO_DATE function.

SELECT STR_TO_DATE('10/10/2020', '%m/%d/%y') + INTERVAL 1 DAY;

So 

SELECT STR_TO_DATE(creationdate, '%m/%d/%y') + INTERVAL 1 DAY;

So if you want to update your query would be

UPDATE table_name
SET creationdate = STR_TO_DATE(creationdate, '%m/%d/%y') + INTERVAL 1 DAY
WHERE Priority = 'P0';

edited Oct 02 '20 at 16:55

answered Oct 02 '20 at 16:47

Dark Knight

kish · Answer 2 · 2020-10-02T16:55:19.620

0

To convert string into date please check below answer:

Considering you are using mssql server. You can use: DATEADD(interval, number, date)

Eg:

Considering you are using MySQL:

Syntax: DATE_ADD(date, INTERVAL value addunit)

Eg: SELECT DATE_ADD("2017-06-15", INTERVAL 1 DAY);

Will add 1 day in date.

edited Oct 02 '20 at 16:55

answered Oct 02 '20 at 16:46

kish

the column name is creationdate, and each one has priority and interval, and the date column is stored as sting , do i have to change it to date first and then do case statmen on it? – Ramin Rafat Oct 02 '20 at 16:48
This answer is for `MSSQL Server`. @RaminRafat, as per question it says `mysql`, please confirm the same – Dark Knight Oct 02 '20 at 16:49
the error that this returns is about the data type. it says that interval can not be working on sting – Ramin Rafat Oct 02 '20 at 16:50
You need to change string into date first – kish Oct 02 '20 at 16:52

score 0 · Answer 3 · answered Oct 02 '20 at 16:51

You can turn the string to a date, and then use date arithmetics. Consider:

select t.*, 
    str_to_date(creationdate, '%d/%m/%Y') + interval intval day enddate
from mytable t

Notes:

your sample data is ambiguous about the format of the date: it could be %d/%m/%Y or %m/%d/%Y
interval is nt a good choice for a column name, because it conflicts with SQL keyword interval (in MySQL, that's a reserved word); I renamed the column intval in the query

3 Answers3