problem in querry-using two dates with between operator

Question

0.00/5 (No votes)

See more:

Hi,
I want to check a particular date between two datetime values.

For Example
if

C#

startdate = 2012-04-23 00:00:00.000
enddate = 2012-04-26 00:00:00.000

i am using this querry-

C#

select leaveemployeeid from leave where '23/04/2012' between convert(VARCHAR, startdate, 103) and convert(VARCHAR, enddate, 103);

it works fine.

but if enddate is to next month from startdate then this querry return null.

for example

C#

startdate = 2012-04-23 00:00:00.000
enddate = 2012-05-04 00:00:00.000

querry return null.

Thanks in Advance.

Posted 25-Apr-12 21:14pm

mayankshrivastava

Add a Solution

3 solutions

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)

deeptibansal · Answer 1 · 2012-04-25T21:26:00

Solution 1

This may happen because by converting the dates to varchar you are doing a string comparision. Try this by not converting the dates to varchar.

[Edit]
something like this

SQL

select leaveemployeeid from leave where convert(datetime,'23/04/2012') between convert(datetime,'2012-04-23 00:00:00.000') and convert(datetime,'2012-05-04 00:00:00.000')
or
select leaveemployeeid from leave where convert(datetime,'23/04/2012') between startdate  and enddate 
-- where startdate and enddate are date fields.

[/Edit]

Posted 25-Apr-12 21:26pm

deeptibansal

Updated 25-Apr-12 22:20pm

v2

Comments

mayankshrivastava 26-Apr-12 4:27am

gives the following error-
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

deeptibansal 26-Apr-12 5:17am

Try this:

select leaveemployeeid from leave where
where convert(datetime,'23/04/2012',103)
between convert(datetime,'2012-23-04 00:00:00.000',103) and
convert(datetime,'2012-04-05 00:00:00.000',103)

mayankshrivastava 27-Apr-12 2:12am

It works Fine.
Thanks DEEPTI :)

deeptibansal 27-Apr-12 3:11am

:)

Praveen Meghwal · Answer 2 · 2012-04-25T21:36:00

Solution 2

If you want to use between operator to compare date, in end date you will have to set maximum time.
Like enddate = 2012-05-04 23:59:59.999

Posted 25-Apr-12 21:36pm

Praveen Meghwal

Sandeep Mewara · Answer 3 · 2012-04-26T08:56:00

Solution 3

You should always use parameterized query. It not just avoid SQL Injection attack, but also maintains code clean and easy.

Try:

SQL

DECLARE @leaveDate DateTime
@leaveDate = '23/04/2012'
select leaveemployeeid from leave where @leaveDate between startdate and enddate

Posted 26-Apr-12 8:56am

Sandeep Mewara