Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: SQL Statement

  1. #1
    New Coder
    Join Date
    Jan 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement

    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?

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    strSQL = "SELECT * from Stats " _
    & " where LastVisit Like '" & "'%" & sDate & "'" _
    & " Order By LastVisit Desc"

    fv

  • #3
    New Coder
    Join Date
    Jan 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    New to the CF scene
    Join Date
    May 2006
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •