Conversion failed when converting date and/or time from character string.

Question

hi this code blow give me this error

Conversion failed when converting date and/or time from character string.

 

code

sqlstr = "select excute_main.*,cycle_main.cust_id,customers.name2 from excute_main,customers,cycle_main where date2 between '" & Format(MaskedTextBox1.Text, "MM-dd-yyyy") & "' and '" & Format(MaskedTextBox2.Text, "MM-dd-yyyy") & "' and excute_main.main_id=cycle_main.id and cycle_main.cust_id=customers.id"

Answer 1

all i need search between 2 dates with this query up there

 

Answer 2

Hi,

if you use sqlparameters instead of concatenating your sql string, then you would never have any problems concerning locale-specific formatting.

 

sqlstr = "select excute_main.*,cycle_main.cust_id,customers.name2 from excute_main,customers,cycle_main where date2 between @date1 and @date2 and excute_main.main_id=cycle_main.id and cycle_main.cust_id=customers.id"

dim param1 as SqlParameter = yourSQLCommand.Parameters.Add( "@Date1", SqlDbType.DateTime,... )
param1.Value = datetimepicker1.value

dim param2 as SqlParameter = yourSQLCommand.Parameters.Add( "@Date2", SqlDbType.DateTime,... )
param2.Value = datetimepicker2.value

use datetimepicker controls instead of maskededitboxes, they'll give you a value property that is of DateTime type, which is locale independent.

---

Regards, Nico

Answer 3

i did it like that

 

 sqlstr = "select excute_main.*,cycle_main.cust_id,customers.name2 from excute_main,customers,cycle_main where date2 between '" + MaskedTextBox1.Text + "' and '" + MaskedTextBox2.Text + "' and excute_main.main_id=cycle_main.id and cycle_main.cust_id=customers.id"

 

and it works

 

i dunno if your code is working also

 

thanks any way :)