Between Condition In Sql Server

Question

0.00/5 (No votes)

See more:

Hi All,

My Query IS

SQL

SELECT * FROM TableName WHERE Date Between Date1 AND Date2 AND ID=ISNULL(@ID,ID)

Here I have avoided dynamic query by using isnull in where condition.. Now i can search using or Date .. When Date is Not Passed the Date Between Date1 AND Date2 this have to pass... that is the case friends..

when date is null .. the condition should have to based on ID only. and between case should pass as true.. Hope U understand!!!!

Thanks,
SarathKumar.N

Posted 10-Jan-13 2:20am

Sarathkumar Nallathamby

Updated 10-Jan-13 17:19pm

v3

Add a Solution

Comments

Sandeep Mewara 10-Jan-13 8:53am

If you mean date as empty == NULL then you can use DBNull for it. Not too clear on what are you trying to do.

4 solutions

Add a Solution

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

Sarathkumar Nallathamby · Answer 1 · 2013-01-10T21:06:00

Solution 4

Hi Friends,

Thank u for ur help.... And I have Solved this

SQL

SELECT * FROM tablename WHERE   Date BETWEEN  ISNULL(@Fromdate,Date) AND ISNULL(@ToDate,ISNULL(@Fromdate,Date))
             AND ID =ISNULL(@Id,ID)

This Query Worked Out For Me.. Thanks Friends for ur contribution....

Posted 10-Jan-13 21:06pm

Sarathkumar Nallathamby

Om Prakash Pant · Answer 2 · 2013-01-10T02:47:00

Solution 1

You can pass null value to the procedure. please check the following thread:

http://stackoverflow.com/questions/1207404/how-to-pass-a-null-variable-to-a-sql-stored-procedure-from-c-net-code[^]

Posted 10-Jan-13 2:47am

Om Prakash Pant

Aarti Meswania · Answer 3 · 2013-01-10T19:09:00

Solution 2

this trick will work

SQL

select * from a 
where 
(startdate between @t1 and @t2   or  (@t1 is null or @t2 is null)) 
and 
(id=@Id or id is null)

Happy Coding!
:)

Posted 10-Jan-13 19:09pm

Aarti Meswania

Subrata.In · Answer 4 · 2013-01-10T20:10:00

Solution 3

If I understand your query then you have solved your requirement on your query. Just modifying your query only. Check it:

SELECT * FROM TableName WHERE IsNull(@Date,Date1) Between Date1 AND Date2 AND ID=ISNULL(@ID,ID)

Posted 10-Jan-13 20:10pm

Subrata.In

Between Condition In Sql Server

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Between Condition In Sql Server

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us