Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Dates fall between two dates

I have a select query with appt_date that should fall between two dates,

start_date
End_date
Appt_date

If the appt_date is between estart_date and End_date, then yes, else, no. example:

appt_date is 01/01/2012
start_date 01/01/2012
end_date 02/03/2012

then this will be a yes.

the statement I have:

select Staff_name,Staff_ID, "I would like to place the if Statement here, appt_date from....
0
real9555
Asked:
real9555
2 Solutions
 
mankowitzCommented:
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
0
 
lwadwellCommented:
CASE WHEN appt_date BETWEEN start_date AND end_date THEN 'yes' ELSE 'no' END
0
 
David KrollCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
real9555Author Commented:
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".
0
 
David KrollCommented:
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 appt_date BETWEEN start_date AND end_date THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename
0
 
real9555Author Commented:
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. :(
0
 
David KrollCommented:
You have the end date as 00:00:00, but the appt_date is 10:00:00.  That is after the end_date.
0
 
David KrollCommented:
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
0
 
real9555Author Commented:
dkrollCTN,

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

should I add another column for appt date and make it date only field?
0
 
David KrollCommented:
can you give me the start, end and appt date you're using?
0
 
David KrollCommented:
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
0
 
real9555Author Commented:
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
0
 
David KrollCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you want that the "end_date" is actually taken fully (aka 23:59,59), just modify above query like this (no points for me):

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 appt_date >= start_date and appt_date < dateadd(day, 1, end_date) THEN 'Yes'
  ELSE 'No'
 END as betweendates,
appt_date
FROM tablename 

Open in new window


see also:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now