SQL SELECT....WHERE Date BETWEEN Dates?

The description would seem to suggest that the answer is infact correct.

You want 1 day (not counting public holidays) notice of an appointment for the 2 jan 2003.   that means you haveto give notice today doesnt it?

Try this to prove my theory:

select 'Good' where CONVERT(varchar(25), DateAdd(day,1, GetDate()), 101) between
CONVERT(varchar(25), GETDATE(), 101) and
 CONVERT(varchar(25), DateAdd(day,2, GetDate()), 101)


select 'Good' where DateAdd(day,1, GetDate()) between
 GETDATE() and
  DateAdd(day,2, GetDate())

Hmm..


Not sure what "theory" you are trying to prove here.....

That now is between now and 2 days later

or

 that now is not between this time tomorrow and this time in two days.

...confused....

ignore last.

ok acampoma I see what you mean..

but with in my code, how would I compare just the mm/dd/yyyy where the time has no meaning, I don't want to miss a notification at 4pm because the appointment is at 5pm that is the reason I was converting to that format

Ah i c what you are getting at now acam.    I think New years SQL dyslexia was setting into me then.

That is a good point and I agree that without a doubt this will mess up the results.  but im also confused as to how the answer as described is wrong in the first place.

I don't think my answer was wrong in the first place, other than the fact it is comparing strings instead of dates

so I need to figure out how to keep the dates I have, and not worry about the time part

12/31/2003 9:00am  BETWEEN 12/31/2003 10:00am AND 1/2/2004 10:00am



See what I mean?  If I ran my app at 9am, the 10am appointment would not be seen

If you dont want time to be a part of it then compare

floor(convert(float,getdate()))
between
floor(convert(float,dbo.fn_Confirm_Date(dbo.v_Appointments_Active.Appointment_Log_id,
dbo.Client_Confirmation_Configuration.confirm_days_in_advance)))
and
floor(convert(float,dbo.v_Appointments_Active.Appointment_Date_Time))

explanation:


datetime fields are comprised of two integers which can be treat as a float   where   the number before the  .  is the date and the number after the . is the time.

so by making it an integer you can compare effectively day numbers.

Or you could of course convert the varchars back to dates again but its messy

Thanks guys

I can now have a happy new year!



increased the points to 100 and split them for ya guys

"I can now have a happy new year!"

yep.   have a good one.

yes,
Have a happy new year as well ShogunWade and chergenroeder

I have Microsoft SQL server 8.0, its located in a high end server, but when i use enterprise manager or query analyzer it workrs really really slow.  is there something wrong with it or is there something that can be done to optimize its performance