Solved

Calculate SQL Server Date Range

Posted on 2011-09-15
4
230 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now