Solved

Calculate SQL Server Date Range

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 63
sql query 5 57
Need multiple Group By's 8 55
Sql Query to return all values in Capital letters 2 8
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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