Data between two Dates does not fetch correctly

Question

0.00/5 (No votes)

See more:

SQL

SELECT     FileNo, ReqControlNo, Requester, ReqDate, ReqTime, IssueDate, IssueTime, DueDate, RequesterDeptt, HandOverTo, ReceivedDate,
                      ProjectName
FROM         Issue
WHERE     (format(IssueDate, 'dd-mm-yyyy') BETWEEN format(?, 'dd-mm-yyyy') AND format(?, 'dd-mm-yyyy'))

I have taken IssueDate as text In Table.
When I find data between same date it retrieves right results.
But when I find data between different date It retrieves Wrong results.
For Example:
From 25-07-2011 to 25-07-2011 returns Right results.
From 23-07-2011 to 25-07-2011 returns wrong results, It picks result of between 23 to 25.It does not check Months.
Please Help me.

Posted 24-Jul-11 19:34pm

uspatel

Add a Solution

Comments

Mycroft Holmes 25-Jul-11 1:40am

What database are you using, you should not need formatting in a select statement.

uspatel 25-Jul-11 1:53am

MS Access 2007

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)

Mycroft Holmes · Answer 1 · 2011-07-24T19:42:00

Solution 1

Always, ALWAYS use unambiguous date formats either yyyy-mm-dd or dd-MMM-yyyy.

Posted 24-Jul-11 19:42pm

Mycroft Holmes

Comments

uspatel 25-Jul-11 1:53am

I have already used dd-mm-yyyy formate.

Mycroft Holmes 25-Jul-11 1:56am

Sure you did and it is WRONG, try using dd-MMM-yyyy, the 3 cap Ms will force a text month into the query.

uspatel 25-Jul-11 2:05am

But I take dd-mm-yyyy format in access database.It contains more than 8000 records.
Can this format be change without affecting my application?

Mycroft Holmes 25-Jul-11 2:15am

Please don't tell me your date data is stored as text values. If so then you URGENTLY need to change to the proper datetime format, if you don't then there is no way to help you.