Problem While Searching BetweenTwo Dates

Question

0.00/5 (No votes)

See more:

Dear Friends

I am working on a project in VB.Net. I am using MS Access as database.
I am having a serious problem while searching the records between two dates.

When I Search the records between two dates it does not give me the complete records but It Missed some of the dates between the given dates.

The Columns in MS Access in which I have stored the dates, is also in date format.
The Dates in MS Access are in "M/d/yyyy" Format.

I have used two DateTimePicker with the name of dtpFrom and dtpTo.

I have used the following sql Query to search between these two dates.

"Select stdnames from tblStudents where AdmDate >= '" & format((Cdate(dtpFrom.value)),"M/d/yyyy") & "' and AdmDate <= '" & format((Cdate(dtpTo.value)),"M/d/yyyy") & "'"

Using This query, I got the result but it missed some of the dates. So, I wan to get all the dates between these two given dates. So, Is there any mechanism that can give me the precise dates between the given dates without missing any of the date.

Waiting for response.

Posted 5-Aug-10 21:34pm

rashidfarooq

Add a Solution

Comments

tiggerc 6-Aug-10 3:41am

try - Select stdnames from tblStudents where AdmDate >= '" & dtpFrom.value.tostring & "' and AdmDate <= '" & dtpTo.value & "'"

it should auto convert the string to date.

3 solutions

Solution 2

American Date format often gives odd results.

Depending on the search applied to it many things can be missed, but using the scientific date format should negate the problem.

Posted 5-Aug-10 21:51pm

Dalek Dave

Comments

rashidfarooq 6-Aug-10 3:57am

What is the scientific date format

[no name] 6-Aug-10 12:24pm

Scientific date referred to is YYYYMMDD. This is independent of culture and is easily sortable.

rashidfarooq 9-Aug-10 1:17am

Thanks Brother. When I used Scientific Date Format I got the Correct Result.
Thanks

Solution 3

DateTime DT1 = new DateTime();
DT1 = DateTime.Parse(dtpFrom.value);
string FrmDate= DT1.ToString("yyyy/MM/dd");

DateTime DT2 = new DateTime();
DT2 = DateTime.Parse(dtpTo.value);
string ToDate= DT2.ToString("yyyy/MM/dd");

"Select stdnames from tblStudents where AdmDate >= '" & FrmDate & "' and AdmDate <= '" & ToDate & "'"

Posted 6-Aug-10 2:54am

ramakrishnankt

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)

Goutam Patra · Accepted Answer · 2010-08-05T21:44:00

Solution 1

Convert your dates into YYYY-MM-DD format and use

"Select stdnames from tblStudents where AdmDate >= CDATE('" & DtFrom & "') and AdmDate <= cdate('" & DTTo & "') "

Posted 5-Aug-10 21:44pm

Goutam Patra

Comments

rashidfarooq 6-Aug-10 3:58am

Let me Try your code

rashidfarooq 9-Aug-10 1:21am

Thanks Brother.
I have used the following string using YYYY-MM-DD Format
"Select stdnames from tblStudents where Format((CDate(AdmDate)),'yyyy-MM-dd') >= '" & Format(DtFrom.value,"yyyy-MM-dd") & "' and Format((CDate(AdmDate)),'yyyy-MM-dd') <= '" & Format(DtFrom.value,"yyyy-MM-dd") & "'"
Using This string I got 100% Result.
So Thanks.