Sql Query To get data

Question

hi guys, i have a table called DrTimings ,in which Doctors time for each day is this. **

DrID(int identity)  StartTime(DateTime)             EndTime(DateTime)
    1                3/31/2009 8:00:00 AM   3/31/2009 10:30:00 AM

**

I have another table called TimeList which contain 3 fields

SlotID   SlotStartTime(varchar)   SlotEndTime(varchar)
1         8:00AM                      8:15AM
2         8:15AM                      8:30AM
3         8:30AM                     8:45AM

and goes on till 7:00PM

I have another table DrDutyDetails in which DrTiming is set for whole month like this.

DrID    StartTime             EndTime              DateOfAllocation
1       4/5/2009 8:00:00 AM   4/5/2009 9:30:00 AM   4/5/2009 12:00:00 AM
2       4/12/2009 8:00:00 AM  4/12/2009 9:30:00 AM  4/12/2009 12:00:00 AM
likewise

I have another table called AppointmentSchedule which gets filled when patient requests appointment

AppointmentID    SlotID  AppointmentDate
1                1        4/5/2009 
2                3        4/12/2009

i have a appointment schedule form in which i selects DrID and Appointment date.Then slots corresponding to date that has not been in Appointmentschedule,but slots should be in between starttime and endtime that in DrTimings and in TimeSlots should get filled in a dropdownlist.I need a query to implement this. for eg:If Patient enters '4/5/2009' Then slots other than 1 and 3 but biw DrTimings i want to get.

Similarly i want to search by time.Means if Patient enters any time b/w DrTimings for eg:8:30AM,Then available first date has to be displayed in textbox

Answer 1

You are going to need to look at doing some Casting/COnverting of date values. This MSDN article gives you a bit of information on what you need to work with.

There are a number of ways that you can do this, but ideally you can do something like CONVERT(VARCHAR(50), StartTime) to get the fully formatted time. Then, since you know that the ending format of that is 08:00AM, you can do a RIGHT(CONVERT(VARCHAR(50), StartTime), 7), which will get you just the time value.

Now, with that you know how to manipulate the date into parts, you can from here, do the various things needed to query your items.

For comparison sake, I would be storing the "TimeList" data as DateTime values, that way you can use standard time comparisons to actually allow you to use functions such as "Between" and similar. You could store them as 1/1/1900 08:00AM or similar. But this would require a data model change. If you want some helpful SQL for this type of thing, here is a great article on "Essential SQL Server Date, Time, and DateTime Functions".