Solved

Calculate SQL Server Date Range

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

13 Experts available now in Live!

Get 1:1 Help Now