PDA

View Full Version : SQL Statement



BrockLesnar
Jan 29th, 2005, 08:18 PM
I'm running MS SQL 2000


I tried to do this

strSQL = "SELECT * from Stats where instr(lastvisit, @sDate) Order By LastVisit Desc"
Comm = new sqlCommand(strSQL, Conn)
Comm.Parameters.Add("@sDate", Calendar1.SelectedDate.ToShortDateString())
Conn.Open

Calanendar1 is a .Net Calendar control and the page is in VB.Net. The Lastvisit column in the sql database is datetime type.

But it gives the error
System.Data.SqlClient.SqlException: 'instr' is not a recognized function name.


How do I use the instr function inside the sql statement?

fractalvibes
Jan 29th, 2005, 10:53 PM
strSQL = "SELECT * from Stats " _
& " where LastVisit Like '" & "'%" & sDate & "'" _
& " Order By LastVisit Desc"

fv

BrockLesnar
Jan 30th, 2005, 01:28 AM
It returned nothing.

So I decided to plug this string into the SQL Query Analyzer and it returned this when I ran it:
Your entry cannot be converted to a valid date time value.

The LastVisit column is a date time type. For an example, in one record, the last visit column has 1/29/2005 2:00:28 PM etc.

eralper
May 23rd, 2006, 04:44 PM
Hi,

You can perhaps check the datetime parameter with the date type column.

SELECT * from TableName where DATEDIFF(d, DateField, @date) = 0 Order By DateField Desc

Eralper
http://www.kodyaz.com