sql server - How to pass a parameter to an SQL script to change argument 2 of DATEADD() in VB.net? -

i trying send parameter drop down box sql server script change argument 2 of dateadd():

select distinct orders.order_id, customers.first_name, orders.order_date, payment_details.card_name, payment_details.card_type, payment_details.customer_id            orders inner join                          customers on orders.customer_id = customers.customer_id inner join                          payment_details on orders.payment_id = payment_details.payment_id        (orders.order_date < convert(varchar(20), dateadd(d, convert(dec, @date), { fn curdate() })));   

and in turn populate grid view results:

protected sub button1_click(sender object, e system.eventargs) handles button1.click         dim orderhistorysearch new allin1idtableadapters.orderstableadapter         gridview1.datasource = orderhistorysearch.getdatabyorderhistory(me.dropdownlist1.text)         gridview1.databind()     end sub end class 

however getting error stating:

failed convert parameter value string datetime.

and

string not recognized valid datetime.

however, when working on sql server wizard, query works fine.

i tried converting data type, nothing seems work.

how can solve problem?

i see 2 issues, let me non-critical 1 out of way first:

1. the second argument of dateadd() should integer.

first, why not decimal? try subtracting half day (or hour, minute, etc.) current date , sql server won't subtract 12 hours, it'll round down 0 , return current date. no reason accept non-integers; leaving option open induce errors.

second, if have declared @date parameter integer type, there shouldn't need convert() @ - make sure code feeds integer sql query. way, i'd call less misleading, such @number_of_days.

2. { fn curdate() } odbc function call

the sql server function current date isn't curdate(), getdate(). expression suggested wizard meant make code more portable, allowing run on other sql platforms don't have getdate(), such mysql.

however, function call has bigger problems, such localisation. example, on system, uses european localisation, calling dateadd(day, 1, { fn curdate() }) bugs out , adds month today, because erroneously treats date. if call dateadd(day, 1, convert(date, { fn curdate() })), correct result i.e. tomorrow.

what suspect happening in localisation/configuration, { fn curdate()} returns doesn't parse valid date @ all. if portability isn't concern, best replace function call simple getdate(). in case, try selecting following expressions on server , seeing result each:

{ fn curdate() }  convert(date, { fn curdate() }) dateadd(day, 1, { fn curdate() }) dateadd(day, 1, convert(date, { fn curdate() })) getdate() dateadd(day, 1, getdate())