problem to date range check

Question

0.00/5 (No votes)

See more:

SQL-server-2000

dear sir/ma'am

here is my table=h_leave

date1 date2 hkey
07/11/2013 10/11/2013 1
10/11/2013 11/11/2013 2
15/11/2013 17/11/2013 3

i want that if i fill an application at 09/11/2013 then from database the checking is occur that is any range find
as in 09/11/2013
07/11/2013 10/11/2013
find

so the answer is
date1 date2 hkey
07/11/2013 10/11/2013 1

how such an answer i got

Posted 17-Jun-13 16:56pm

ankur789

Updated 17-Jun-13 17:30pm

v3

Add a Solution

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)

Christian Graus · Answer 1 · 2013-06-17T17:05:00

Solution 1

Wow. Several attempts later, based on how completely lost you were, try this:

select date1, date2, hkey from h_leave where date1 >= @date and date2 <= @date

Replace @date with the date you're checking, either as a param in a proc, or using a paramaterized query.

Posted 17-Jun-13 17:05pm

Christian Graus

Updated 17-Jun-13 17:37pm

v3

Comments

ankur789 17-Jun-13 23:09pm

sir same answer i found
date1
07/11/2013
10/11/2013
15/11/2013

Christian Graus 17-Jun-13 23:14pm

I am not sure exactly what you wanted. Did you want to filter on date2 both times ? If so, change my code to do that.

ankur789 17-Jun-13 23:20pm

sir i select 09/11/2013
then your query will be
select date1 from h_leave
where (date1 between '2013/11/09' and '2013/11/09')
union all
select date2 from h_leave
where (date2 between '2013/11/09' and '2013/11/09')

but please check in my table there is a row between this range the row consists 07/11/2013 as date1 and 10/11/2013 as date2

but fromyour query the answer i got as null

Christian Graus 17-Jun-13 23:26pm

If you don't know enough about SQL to read my SQL and understand it, you should not be doing this. If you want to filter on date2 in both queries, you need this:

select date1 from h_leave
where (date2 between '2013/11/09' and '2013/11/09')
union all
select date2 from h_leave
where (date2 between '2013/11/09' and '2013/11/09')

ankur789 17-Jun-13 23:22pm

while the answer should be
07/11/2013
10/11/2013

because 9 is in this range

ankur789 17-Jun-13 23:29pm

sir what exactly i want is now in updated ques please help me in such a matter,

i m trying to got my answer but failed,

Christian Graus 17-Jun-13 23:31pm

I honestly cannot understand what you're asking. What do you want ?

Christian Graus 17-Jun-13 23:32pm

Do you want to know a range that contains all the values you passed in ? Your SQL is not even close for that. Why are you doing a union ? It looks to me like your starting point is so wrong that it's confusing the question.

ankur789 17-Jun-13 23:35pm

date1 date2 hkey
07/11/2013 10/11/2013 1
10/11/2013 11/11/2013 2
15/11/2013 17/11/2013 3
this is my table

now when i have a form which has two datetime pickers
now i select in both 09/11/2013
so now i want to check is in my table there is a range for 09/11/2013
yes i have the range which is
07/11/2013 10/11/2013 1
so the answer will be this range from my query then what is the query so that i found such a answer

Christian Graus 17-Jun-13 23:36pm

OK - your starting point was a disaster, no wonder I got confused.

ankur789 17-Jun-13 23:38pm

sir now you understood what i want ,now sir what i do for such a answer

Christian Graus 17-Jun-13 23:41pm

I fixed the SQL. Union is almost always a bad idea. In this case, I am lost why you even though you needed it.

ankur789 18-Jun-13 0:54am

what is your advice to do

Christian Graus 18-Jun-13 0:55am

At this point, I gave you a correct solution an hour ago.