mysql DATE_ADD INTERVAL as value

Question

is it possible to use arguments in DATE_ADD as values from some field in table.

SELECT DATE_ADD(some_date, INTERVAL some_number WEEK) as new_date FROM table1

Is it possible to replace "WEEK" by some value from my table. Let's say i have a column with values DAY, WEEK, MONTH etc.?

Ravinder Reddy · Accepted Answer · 2013-09-30 10:01:16Z

1

No. You only can have a dynamic value for the expression but not for the unit keywords on intervals. Unit keywords can be SECOND, DAY_MICROSECOND, etc..
Concatenation of these unit names will result a string expression and can't be evaluated as an interval value. Hence you can't use place holders (prepared statement) to achieve this.

Alternatively you can generate the query by concatenating partial strings, in your programming language.

String part1 = "SELECT DATE_ADD(some_date, INTERVAL some_number ";  
String part2 = "WEEK"; // say as default value  

if( my_condition_1_is_satisfied ) then part2 = "DAY"
else if( my_condition_2_is_satisfied ) then part2 = "MONTH"
else ...

// then concatenate part1 and part 2
String sql = part1 + part2;
// now your query string is ready to use

Refer to: DATE_ADD(date,INTERVAL expr unit)

answered Sep 30, 2013 at 10:01

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql DATE_ADD INTERVAL as value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related