Date format error in vb.net ?

Question

I get this error when I run the application Incorrect syntax near 12, on debugging I found that this error is caused due to the # along with the date.

Dim backdate as datetime
backdate = DateTime.Now.AddDays(-1)

on binding the data to the grid to filter the backdate records this error is caused Incorrect syntax near 12.

myqry =  " select SRNO,SUBJECT,ID where datesend =" backdate 

Now i am trying to extract only the date or shall I divide the date into day , month and year with DATEPART and take into a variable or convert the date or what should i do , Please help ???

Answer 1

Note that you are missing the ampersand (&) in your query. Also, try putting your date in quotes in the query:

myqry =  " select SRNO,SUBJECT,ID where datesend ='" & backdate.ToString('yyyy-MM-dd') & "'"

Answer 2

This is the correct statement:

myqry = "select SRNO,SUBJECT,ID where cast(convert(char(10), datesend, 121) as datetime) ='" & backdate.ToString("yyyy-MM-dd") & "'"

Cast and Convert: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Convert with parameter 121 will convert to the following format: yyyy-mm-dd hh:mi:ss.mmm(24h), from that string, we get the first 10 characters (char(10)).

Without parameters:

myqry = "Select SRNO,SUBJECT,ID From ... Where convert(char(10), datesend, 121) = convert(char(10), dateadd(day,-1,getdate()), 121)"

Answer 3

myqry = " select SRNO,SUBJECT,ID where datesend = '" & backdate.tostring("dd MMMM yyyy") & "'"