2

how to search data from month to month if :

txtbox1.text = "m/y" or "06/2013"

txtbox2.text = "m/y" or "09/2013"

mysql query code :

SELECT * FROM tb_user WHERE dateregister ?.....

and if, record in table is:

id name dateregister

1 abc 05/05/2013

2 ccc 06/05/2013

3 ddd 01/06/2013

4 ggg 01/07/2013

5 ttt 10/07/2013

6 kkm 20/08/2013

7 ooo 01/09/2013

and I just wanted to take the data from month "06/2013" to "09/2013" and data that I get is:

3 ddd 01/06/2013

4 ggg 01/07/2013

5 ttt 10/07/2013

6 kkm 20/08/2013

7 ooo 01/09/2013

I've tried a variety of query but I always fail..

this is my code in vb.net :

Imports MySql.Data.MySqlClient

Dim connString As String = "Database=dbuser;Data Source=localhost;User Id=root;Password="

Dim conn As New MySqlConnection(connString)

Dim adapter As New MySqlDataAdapter

Dim ds As New DSReportPO

Dim cmd As MySqlCommand

monthx1= txtbox1.text

monthx2 = txtbox2.text

cmd = New MySqlCommand("SELECT * FROM tb_user WHERE dateregister?...", conn)

adapter.SelectCommand = cmd

adapter.Fill(ds.Tables(0))

Frm1.Show()

Frm1.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local

Frm1.ReportViewer1.LocalReport.ReportPath = System.Environment.CurrentDirectory & "\Report1.rdlc"

Frm1.ReportViewer1.LocalReport.DataSources.Clear()

Frm1.ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("DSPOListSETUP", ds.Tables(0)))

Frm1.ReportViewer1.DocumentMapCollapsed = True

Frm1.ReportViewer1.RefreshReport()

thanks a lot

Community
  • 1
  • 1
Riski Febriansyah
  • 335
  • 1
  • 8
  • 21

4 Answers4

2

You just have to use the BETWEEN keyword.

 SELECT * FROM tb_user 
 WHERE dateregister between @textBox1Value and @textBox2Value

Make sure the dates you parsing (@textBox1Value and @textBox2Value) is well typed.

Refer to this link for the date formatting.

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • thanks i've tried.. and and there is an error "MysqlException was unhandled" the code: cmd = New MySqlCommand("SELECT * FROM tb_user WHERE dateregister BETWEEN (@TextBox2.text and @TextBox3.text)", conn)adapter.SelectCommand = cmd adapter.Fill(ds.Tables(0)) <- error in here – Riski Febriansyah Sep 17 '13 at 07:33
  • Your date should be well formatted. In your database also, your date should be of type `Datetime` as it make no sense to use the `between` when we using `varchar`. You can't have a condition saying between "PAUL" and "PAULA" :) – Nadeem_MK Sep 17 '13 at 08:33
2
SELECT  *
FROM    tableName
WHERE   STR_TO_DATE(dateregister, '%d/%m/%Y')
        BETWEEN STR_TO_DATE(CONCAT('01/', '06/2013'), '%d/%m/%Y') AND
                LAST_DAY(STR_TO_DATE(CONCAT('01/', '09/2013'), '%d/%m/%Y'))

OUTPUT

╔════╦══════╦══════════════╗
║ ID ║ NAME ║ DATEREGISTER ║
╠════╬══════╬══════════════╣
║  3 ║ ddd  ║ 01/06/2013   ║
║  4 ║ ggg  ║ 01/07/2013   ║
║  5 ║ ttt  ║ 10/07/2013   ║
║  6 ║ kkm  ║ 20/08/2013   ║
║  7 ║ ooo  ║ 01/09/2013   ║
╚════╩══════╩══════════════╝

Here's what happened to the query above, the column dateregister was converted into proper date using STR_TO_DATE() since it was saved as string in the format of dd/mm/YYYY.

Your input 06/2013 and 09/2013 were concatenated with 01/ and converted into date just like how the column dateregister was converted.

The function LAST_DAY takes a valid date and returns the last date of the month.

If you need faster performance in terms of searching for dates, you need to change the data type of column dateregister into DATETIME or DATE data type to avoid conversion.

Other References

monthx1= txtbox1.text
monthx2 = txtbox2.text
Dim  sqlQuery As new System.Text.StringBuilder
sqlQuery.Append("SELECT * " & vbCrLf)
sqlQuery.Append("FROM   tablename " & vbCrLf)
sqlQuery.Append("WHERE  Str_to_date(dateregister, '%d/%m/%Y') BETWEEN " & vbCrLf)
sqlQuery.Append("       Str_to_date(Concat('01/', " & vbCrLf)
sqlQuery.Append("                   '" & monthx1 & "'), '%d/%m/%Y') AND Last_day( " & vbCrLf)
sqlQuery.Append("              Str_to_date(Concat('01/', " & vbCrLf)
sqlQuery.Append("                          '" & monthx2 & "'), '%d/%m/%Y')) ")
cmd = New MySqlCommand(sqlQuery.ToString(), conn)
John Woo
  • 258,903
  • 69
  • 498
  • 492
1

You can use BETWEEN to find the date in range and use DATE_FORMAT to format date in query not sure about VB syntax

  SELECT * FROM tb_user WHERE
  DATE_FORMAT(STR_TO_DATE(dateregister, '%d/%m/%Y'),'%m/%Y') 
  BETWEEN txtbox1.text AND  txtbox2.text

OR

  SELECT * FROM tb_user WHERE
  DATE_FORMAT(STR_TO_DATE(dateregister, '%d/%m/%Y'),'%m/%Y') 
  BETWEEN '06/2013' AND  '09/2013'

Try this fiddle

Mysql Date format

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • thanks, I've tried it.. no error but cannot give a result : The code that I use "SELECT * FROM tb_user WHERE DATE_FORMAT(dateregister,'%m/%Y') BETWEEN '%" & txtbox1.text & "%' and '%" & txtbox2.text & "%'" – Riski Febriansyah Sep 17 '13 at 06:29
  • @dianju : wow..in sqlfiddle.com/#!2/f7eeef/2 it's works, but I have omitted the symbol % to '"& txtbox.text &"' and still no results, then I try to change '"& txtbox.text &"' directly with '06 / 2013 'and '09 / 2013 'and do not give result to.hmm.. what is wrong? but if I just look for the data in 1 month using MYSQL Query Code : SELECT * FROM tb_user WHERE dateregister LIKE '%" & txtbox1.text & "%' I got it .. – Riski Febriansyah Sep 17 '13 at 07:20
  • @RiskiFebriansyah As i have provided the clear example in fiddle but i don't know the VB.net syntax so i can't tell you whats the problem :) – M Khalid Junaid Sep 17 '13 at 07:36
  • @RiskiFebriansyah try this one http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i may it will help you – M Khalid Junaid Sep 17 '13 at 07:46
1

The efficient way to handle TIME part also is to re-write the code as below

SELECT * FROM tb_user 
WHERE 
dateregister >= str_to_date(concat('01/',@textBox1Value ),'%d/%m/%Y') and 
dateregister < date_add(str_to_date(concat('01/',@textBox1Value ),'%d/%m/%Y'),interval 1 month)
Madhivanan
  • 13,470
  • 1
  • 24
  • 29