CODEWITHSUNDEEP
mysqlvb.net
user3460287
user3460287

Reputation: 61

VB.net mySQL How to use DateAdd in sql query

I'm getting an error when the form load and it said FUNCTION databasename.DateAdd does not exist

con.Open()
cmd.Connection = con
cmd.CommandText = "update pawn set status = 'Renewed', date_added = DateAdd(month,4,date_added), first_date = DateAdd(month,5,first_date), second_date = DateAdd(month,6,second_date), due_date = DateAdd(month,7,due_date)"
dr = cmd.ExecuteReader
con.Close()

Upvotes: 0

Views: 502

Answers (2)

Vivek S.
Vivek S.

Reputation: 21885

You've used VB.NET's DateAdd() inside the query which won't work for MySQL because MySQL doesn't has an inbuilt function like that

Syntax for MySQL date add function is DATE_ADD(date,INTERVAL expr type)

   cmd.CommandText = "update pawn set status = 'Renewed' " & _
                     ",date_added = DATE_ADD(date_added,INTERVAL 4 MONTH)" & _
                     ",first_date = DATE_ADD(first_date,INTERVAL 5 MONTH)" & _
                     ",second_date = DATE_ADD(second_date,INTERVAL 6 MONTH)" & _
                     ",due_date = DATE_ADD(due_date,INTERVAL 7 MONTH)"

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

You can do something like below-

ADDDATE(date_column, INTERVAL 4 YEAR)
ADDDATE(date_column, INTERVAL 4 MONTH)
ADDDATE(date_column, INTERVAL 4 DAY)

Upvotes: 1

Related Questions