How to Search between Dates EXACTLY

Question

3.00/5 (1 vote)

See more:

I have a problem in my searching between 2 dates, when i choose from date = 9/1/2014 , UpTo = 9/5/2014, its working but not exact dates.. it displayed 9/1/2014 to 9/16/2014. any help will appreciate Thanks

here's my code:

VB

Dim sqlstring As String = "SELECT * FROM Reports WHERE Dates>=CDate( ' " & From.Text & "') AND Dates<=CDate('" & UpTo.Text & "')"

           com = New OleDbCommand(sqlstring, conn)
           Dim oledbdataadapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlstring, conn)
           Dim myDataSet As DataSet = New DataSet()
           oledbdataadapter1.Fill(myDataSet, "Reports")
           DataGridView1.DataSource = myDataSet.Tables("Reports").DefaultView

Posted 16-Sep-14 18:16pm

TheHellGirl

Updated 16-Sep-14 18:53pm

_Maxxx_

v2

Add a Solution

Comments

SRS(The Coder) 17-Sep-14 0:51am

Please check with the date format once as i guess it is taking the different format like 'dd/mm/yyyy' rather than 'mm/dd/yyyy' what we are expecting, so it is including dates after 5th September also. Please check once and let me know if this is the case otherwise we will analyse more on the problem to get the cause.

TheHellGirl 17-Sep-14 1:20am

my date format is mm/dd/yyyy,i think there is no problem with my dateformat,

when i choose from date = 9/1/2014 upto = 9/5/2014, it displayed the following:
9/1/2014
9/2/2014
9/3/2014
9/4/2014
9/5/2014
9/10/2014
9/11/2014
9/12/2014
9/16/2014

i have all data from 8/24/2014 upto 9/16/2014

SRS(The Coder) 17-Sep-14 2:04am

Yes your date format is correct but please check with default system date format it might be 'dd/mm/yyyy'. Or you can check with explicitly mentioning the date format to compare in the expression you mentioned above, otherwise there is no way it will fail to calculate the between range for date.

TheHellGirl 17-Sep-14 2:47am

Its work fine now... THANKS A LOT :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Maxxx_ · Accepted Answer · 2014-09-16T18:56:00

This is what I would do:

Run it in debug with a breakpoint after the line that builds the SQL string

When it breaks, look at the value of the string, copy it then paste it in SSMS and run it there

As SRS says, it is likely the format of the date strings you are using - so you can fix that in SSMS easily - see if it works, then apply that change to either the database or the application.

incidentally, when using dates on SQL, I generally use the format '13-Jan-2014' to avoid any confusion.