Records Between two date columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Records Between two date columns

Author

Topic

rajesh_
Starting Member

8 Posts

Posted - 2007-05-02 : 14:15:13

Hi All,
I am having a problem getting records between two date columns. My table is shown below.

SAP_ID JoinDate LeaveDate
1 2006-01-23 00:00:00.000 2006-04-12 00:00:00.000
2 2006-09-04 00:00:00.000 2007-10-10 00:00:00.000
3 2006-10-03 00:00:00.000 2007-01-01 00:00:00.000
4 2007-12-04 00:00:00.000 2007-10-10 00:00:00.000

From asp page i am getting joindate and leavedate . I need to show records between joindate and leavedate.

For ex:
If
JoinDate=2006-01-01
LeaveDate=2007-01-01
then

report should show 1,2,3 SAP No's. But My Query showing only 1, 3. Here is the my query.

select sap_id from tblsap where joindate >= '2006-01-01' and leavedate <='2007-01-01'

I am not able to figure out the problem. Can some one please post suggestions to my problem.

Rajesh

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 14:21:35

I think SQL Server is right in returning only those 2 records..those are theo nly 2 rows that match your conditions

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rajesh_
Starting Member

8 Posts

Posted - 2007-05-02 : 14:25:14

yes....but it has to show three records.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-02 : 14:27:51

quote:
Originally posted by rajesh_

yes....but it has to show three records.

Or maybe you wanted a OR condition ?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 14:30:10

yes if you use an OR, you will get records matching EITHER condition, but since you are using an AND only rows matching BOTH conditions are retrieved.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources