locked
sql query between dates RRS feed

  • Question

  • i have a form that needs to run a query on the button click that queries between dates and seem to have a problem with it.

    i'm using

    sql.CommandText = "SELECT * FROM mytablename WHERE column1 between '" & datetimepicker1.value & "' AND '" & datetimepicker2.value & "';"

    this returns no data.

    if i do it for one date like

    sql.CommandText = "SELECT * FROM mytablename WHERE column1 like '" & datetimepicker1.value & "'   

    it works ok...so what am i missing in my between statement?          
               

    Tuesday, March 23, 2010 8:41 PM

Answers

  • sql.CommandText = "SELECT * FROM tablename WHERE date >= #" & DateTimePicker1.Value.Date & "# AND date <= #" & DateTimePicker2.Value.Date & "#"
    • Marked as answer by xp_user33 Wednesday, March 24, 2010 11:54 AM
    Wednesday, March 24, 2010 11:51 AM

All replies

  • sql.CommandText = "SELECT * FROM mytablename WHERE myDate >= " & datetimepicker1.Value &     "AND myDate <= " & datetimepicker2.Value
    By the way, are you using Access or SQL Server? The above code snippet is untested.
    Only performance counts!
    Tuesday, March 23, 2010 9:01 PM
  • oh..sorry.  the snippet is running in a visual basic form for an acess 2000 table.

    so it's that simple? i don't have to write between?

    just

     where column1 >= " & datetimepicker1.Value &     "AND column1 <= " & datetimepicker2.Value & "   ??

    Tuesday, March 23, 2010 10:37 PM
  • IIRC in Access you need to surround dates with # chars not single quotes.

    So something like this might work.

     

    sql.CommandText = "SELECT * FROM mytablename WHERE column1 between #" & datetimepicker1.value & "# AND #" & datetimepicker2.value & "#;"

    Tuesday, March 23, 2010 11:37 PM
  • Use the parameter classes and avoid all that together.

    Read: Coding Horror: Give me parameterized SQL or give me death


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

    • Proposed as answer by jtorrecilla Wednesday, March 24, 2010 9:46 AM
    Wednesday, March 24, 2010 1:22 AM
  • thanks all.  i will try your suggestions first thing in the morning.

    in my searches to find how to fix my issue, i seen several examples of parameter queries, but they didn't really explain how they worked and that has been my only problem in going forward with trying to learn them.  for my needs, i use datetimepickers and textboxes to run most of the queries on a button click instead of setting say @startdate to be equal to a given date - it would be put in with the textbox so i'd have to say @startdate = datetimepicker1.value somewhere right?

    i'm very much a noob when it comes to sql queries, so jumping right into parameter queries might be the best thing instead of the "old fashioned" way, but i need a real tutorial, not just a quick example that doesn't explain very much.

    would you happen to have a link to a sql parameter queries for dummies guide? or at least a step by step guide? that i can really learn from?

     

    *** and just a side note...i'm not actually programming inside of access - i'm using visual studios 2008 and programming in visual basic.
    Wednesday, March 24, 2010 2:27 AM
  • //example

    Basically, do not hardcode the values directly into the string as you were doing.

    //Incorrect
    sql.CommandText = "SELECT * FROM mytablename WHERE column1 between #" & datetimepicker1.value & "# AND #" & datetimepicker2.value & "#;"
    
    //correct
    Dim sql As String = "SELECT * FROM mytablename WHERE column1 between @Date1 AND @Date2"
    Dim table As New DataTable()
    Using cmd As New SqlCommand(sql, conn)
      cmd.Parameters.AddWithValue("@Date1", datetimepicker1.value)
      cmd.Parameters.AddWithValue("@Date2", datetimepicker2.value)
      Using da As New SqlDataAdapter(cmd)
         da.Fill(table)
      End Using
    End Using
    

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, March 24, 2010 2:38 AM
  • You may have to use instead of AddWithValue cmd.Parameters.Add(..............) Which is more concise for DataTime
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, March 24, 2010 2:39 AM
  • Do a google search on SqlParameter and DateTime

    An older post (In C#, but relevant)


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

    Wednesday, March 24, 2010 2:43 AM
  • Dim PathToDataBase As String = CurDir() & "\mydatabasename.mdb"
    
               
                Dim ConnectionStringToDatabase As String
                ConnectionStringToDatabase = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & _
                                               PathToDataBase & _
                                             ";Persist Security Info=True"
    
               
                Dim cn As New OleDb.OleDbConnection(ConnectionStringToDatabase)
    
                Dim sql As New OleDb.OleDbCommand
                sql.CommandText = "SELECT * FROM mytablename WHERE column1 = '" & textbox1.text & "' AND date like '" & today & "';"
                sql.Connection = cn
                Dim Da As New OleDb.OleDbDataAdapter(sql)
                Dim Dt As New DataTable
                Da.Fill(Dt)
    	    datagridview1.datasource = dt
                counter.text = dt.rows.count
    

    this is a sample of my code.  i used this to fill a datagridview based on the information in a textbox and today's date and it works fine.

    i have a 2nd query that needs to have the same textbox value along with the between dates query..

    so i could say... (starting from where i have my sql command)

    sql.CommandText = "select * from mytablename where column1 = @name and date between @date1 and @date2"

      ///skipping to  datatable

    dim dt as new datatable
    using cmd as new sqlcommand(sql,cn)
    cmd.parameters.addwithvalue("@date1", datetimepicker1.value)
    cmd.parameters.addwithvalue("@date2", datetimepicker2.value)
    cmd.parameters.addwithvalue("@name", textbox1.text)
    using da as new sqldataadapter(cmd) 

    or just keep the oledbdataadapter ? or both?

    please correct me if i misunderstand.

    Wednesday, March 24, 2010 3:03 AM
  • Why don't you extract it into a method? (If you have an existing connection, use that since the default is usually set to pooling anyhow.

    Private Function FillDateBetween() As DataTable
      Dim sql As String = "SELECT * FROM mytablename WHERE column1 between @Date1 AND @Date2"
      Dim table As New DataTable()
      Using cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Date1", datetimepicker1.value)
        cmd.Parameters.AddWithValue("@Date2", datetimepicker2.value)
        Using da As New SqlDataAdapter(cmd)
          da.Fill(table)
        End Using
      End Using
      Return table
    End Function

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, March 24, 2010 3:51 AM
  • since John is doing an excellent job of helping you straighten out your code, I'll post these 2 links for SQL query formats that I've found helpful:

    http://www.w3schools.com/SQL/default.asp

    http://www.1keydata.com/sql/sqlselect.html

     

    note also, if you use DateTimePicker.Value, this includes the Time portion of the Date, so your query may not return any records, especially if your database table includes the time portion also

    if so, try using this to only return the Date portion:

    DateTimePicker1.Value.Date
    Wednesday, March 24, 2010 5:56 AM
  • thanks.

     

    in my database, i have the date column set to short and my datetimepicker in my form is also set to short and in the properties i set it's format to m/dd/yyyy. when i used just one it worked fine, but when i did the between, it didn't work, but like you said thanks to john, i think that will be fixed as soon as i try the code. but i do think just to be safe and make sure it works properly, i should use your suggestion as well.

    Wednesday, March 24, 2010 9:42 AM
  • In agree with John...

     

    Adding only one thing, the delimitter char in access for dates is the # instead of " ' "


    Para el correcto funcionamiento, y que otros usuarios se puedan beneficiar de la solucion de esta pregunta por favor marca las respuestas que te hayan ayudado como "Respuesta".
    Si la respuesta te ha sido util Votala.
    Mi Blog: Jtorrecilla
    Enlace a Faq de Winforms en Ingles Muy bueno
    Wednesday, March 24, 2010 9:48 AM
  • thanks all. i tried using the parameter query and after importing system.data.sqlclient

    i was able to almost get it to work....

    i think where i already have an oledb connection, it doesn't like trying to connect with sql also...i got a sql connection error when trying to run it...

    so i used

    WHERE date >= #" & DateTimePicker1.Value.Date & "# AND date <= #" & DateTimePicker2.Value.Date & "#"

     and it works great...although i am going to look more into parameterized queries.

    thanks again for all your help

    Wednesday, March 24, 2010 11:40 AM
  • sql.CommandText = "SELECT * FROM tablename WHERE date >= #" & DateTimePicker1.Value.Date & "# AND date <= #" & DateTimePicker2.Value.Date & "#"
    • Marked as answer by xp_user33 Wednesday, March 24, 2010 11:54 AM
    Wednesday, March 24, 2010 11:51 AM
  • Try this:

     

    sql.CommandText = "SELECT * FROM tablename WHERE date >= #?# and date<=#?#"
    
    sql.Parameters.AddWithValue("par1",DateTimePicker1.Value.Date )
    sql.Parameters.AddWithValue("par2",DateTimePicker2.Value.Date )

    Para el correcto funcionamiento, y que otros usuarios se puedan beneficiar de la solucion de esta pregunta por favor marca las respuestas que te hayan ayudado como "Respuesta".
    Si la respuesta te ha sido util Votala.
    Mi Blog: Jtorrecilla
    Enlace a Faq de Winforms en Ingles Muy bueno
    Wednesday, March 24, 2010 11:56 AM