SQL - choose number from string, and replace it

Question

I hav the next query in SQL: SELECT id FROM student WHERE DATE LIKE '%$inputdate' the inputDate is in the next format: 2010.08.03 I also want to change the date of the date, by increasing the day in a week (+7).

I try this:

SELECT id
FROM student
WHERE DATE LIKE '%$inputdate'
     OR DATE Like '%CAST(RIGHT("2012-11-03", 2)AS INT) + 7;

But it gives me SQL syntax error. How can I write it correctly?

What SQL engine are you dealing with?

Ahmad
– Ahmad

2012-11-07 11:41:20 +00:00
Commented Nov 7, 2012 at 11:41 — Ahmad
– Ahmad, Commented Nov 7, 2012 at 11:41

John Woo · Accepted Answer · 2012-11-07 12:12:53Z

1

since you are using MySQL, make use of DATE() function

SELECT id
FROM student
WHERE DATE(`DATE`) = DATE(inputdate) OR 
      DATE(`DATE`) = DATE(DATE_ADD(DATE(inputdate),INTERVAL 7 DAY))

SOURCES

edited Nov 7, 2012 at 12:12

answered Nov 7, 2012 at 11:49

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

Adam Sh Over a year ago

Thank you. it still not working, because it like the DATA_ADD. How I do that?

John Woo Over a year ago

do you mean DATE_ADD? what is the data type of DATE in your table? is it date ot stored as varchar?

Adam Sh Over a year ago

I got as an input: 2012-11-03, and I want to find the Id's with there date 2010-11-10. But because I also have the hour, it's like the data column, and not equal.

John Woo Over a year ago

that's why you have to use the function DATE so it will get rid of the time. Let me clarify some things, 1.) what is the format if inputdate? 2.) what is the data type of DATE column, is it DATE or varchar? I also updated the answer.

Collectives™ on Stack Overflow

SQL - choose number from string, and replace it

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related