Solved

Calculate SQL Server Date Range

Posted on 2011-09-15
4
243 Views
Last Modified: 2012-08-14
I have a table with the following schema:

EmployeeID int
StartDate smalldatetime
EndDate smalldatetime

One record in this table contatis the following data:

ID   StartDate        EndDate
1     2010-11-30     2011-05-16

I need to know if the Strat/End Date record is between a range, for example '2011-04-16' and '2011-05-15'.
How can I achieve this with SQL Server ?
0
Comment
Question by:jrthurler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 36545133
select *
from tablename
where startdate between '2011-04-16' and '2011-05-15'
or enddate between '2011-04-16' and '2011-05-15'
0
 

Author Comment

by:jrthurler
ID: 36545191
This will not work for me. What I need is to calculate if the employee is out of work in the period specified (in this case he started in medical license in 2010-11-30 and ended in 2011-05-16).

I need to ensure that in the period from '2011-04-16' until  '2011-05-15' he was in medical license. If I use your approach (which was what I´m using today) the program will not works fine!

Thanks in advance
0
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 36545206
select
  case
    when '2001-04-16' between startdate and enddate and '2011-05-15' between startdate and enddate then 'Yes'
    else 'No'
  end as inmedicallicense
from table
where id = 1
0
 

Author Closing Comment

by:jrthurler
ID: 36545274
Thanks for your help, after so many hours working on this system the logical disappears from us...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question