Dates fall between two dates

have you tried

SELECT .... WHERE appt_date BETWEEN start_date AND end_date

or

SELECT .... WHERE appt_date < end_date AND appt_date>=start_date

CASE WHEN appt_date BETWEEN start_date AND end_date THEN 'yes' ELSE 'no' END

select
  Staff_name,
  Staff_ID,
 CASE
  WHEN appt_date BETWEEN start_date AND end_date THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename

dkrollCTN,

that worked best but what if the start_date  and end date is balnk, can I get the code to give me the word "Null".

Appt_date                                 from date            end_date
2013-02-01 10:00:00      2012-07-26 00:00:00      2013-02-01 00:00:00      No

it gave me no when it should have been yes. :(

You have the end date as 00:00:00, but the appt_date is 10:00:00.  That is after the end_date.

If you just want to use the date part and not worry about the time, do this:

select
  Staff_name,
  Staff_ID,
 CASE
  WHEN (start_date is NULL or start_date = '') and (end_date is NULL or end_date = '') THEN 'Null'
  WHEN convert(varchar, appt_date, 101) BETWEEN convert(varchar, start_date, 101) AND convert(varchar, end_date, 101) THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename

dkrollCTN,

it is still giving me No for yes cases.....

should I add another column for appt date and make it date only field?

can you give me the start, end and appt date you're using?

if you use this, you won't need another column

select
  Staff_name,
  Staff_ID,
 CASE
  WHEN (start_date is NULL or start_date = '') and (end_date is NULL or end_date = '') THEN 'Null'
  WHEN convert(varchar, appt_date, 101) BETWEEN convert(varchar, start_date, 101) AND convert(varchar, end_date, 101) THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename

appt_date                           start_date                         end_date

2013-03-25 09:00:00      2012-09-11 00:00:00      2013-03-25 00:00:00

appt_date         smalldatetime      Checked
start_date        smalldatetime      Checked
end_date                      smalldatetime      Checked

Sorry, I left one thing out, try this:


select
  Staff_name,
  Staff_ID,
 CASE
  WHEN (start_date is NULL or start_date = '') and (end_date is NULL or end_date = '') THEN 'Null'
  WHEN convert(varchar(10), appt_date, 101) BETWEEN convert(varchar(10), start_date, 101) AND convert(varchar(10), end_date, 101) THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename