December 18, 2007 at 10:46 am
I have a start date and end date in my table. I am using a calendar control. i am passing the date to my SP. my application is used for making appointments. For example if someone makes an appointmnet from 11 Pm 12/18/2007 to 4 AM 12/19/2007.
I store thsese two values in my table.
When the user comes and picks up a date form the calendar i pass the date to my SP. Then it shows up the appointment for that day. If the appointment goes from one day to another(11 Pm 12/18/2007 to 4 AM 12/19/2007) and some picks up the date 12/18/2007 it will show the whole appointment form 12/18 to 12/19. 11 pm to 4 am and that is fine.
if someone picks up 12/19/2007 i only want to show that there is an appointment from 12 am to 4 am.
With my SQL it works for the first date 12/18/2007 but if if i pick 12/19/2007 it also shows the whole appointment. i only need to show the appointment for that day only 12 am - 4 am.
Actually it is a join of three tables. But the date is only stored in schedule_info table
here is my SQl
SELECT *, Server_Name.Server_Name AS servername,Schedule_Info.ID AS infoid,Schedule_Info.Booked AS booked,Schedule_Info.end_date AS end_date
FROM Schedule_Info INNER JOIN
Users ON Users.UserID = Schedule_Info.userid INNER JOIN
Server_Name ON Schedule_Info.serverid = Server_Name.Serverid
WHERE (Schedule_Info._Date = '12/17/2007' or Schedule_Info.end_date ='12/17/2007') AND (Schedule_Info.serverid IN
(SELECT Server_Name.Serverid
FROM Users INNER JOIN
userserver ON Users.UserID = userserver.userid INNER JOIN
Server_Name ON userserver.serverid = Server_Name.Serverid
WHERE (Users.Username = 'test') AND approve = 1))
ORDER BY Schedule_Info.serverid
Here is the Table structure
CREATE TABLE [Schedule_Info] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[_Date] [datetime] NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonResponsible] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Starting_Time] [datetime] NOT NULL ,
[Ending_Time] [datetime] NOT NULL ,
[AMPM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Approve] [bit] NULL ,
[userid] [int] NULL ,
[serverid] [int] NULL ,
[Customer_status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[number_of_seats] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Booked] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approve] [bit] NULL ,
[Passwordencrypt] [binary] (16) NULL ,
[Company_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Server_Name] (
[id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Server_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serverid] [int] NOT NULL ,
127.0.0.1 [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Server_Name] PRIMARY KEY CLUSTERED
(
[Serverid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
December 18, 2007 at 11:43 am
When the user comes and picks up a date form the calendar i pass the date to my SP. Then it shows up the appointment for that day. If the appointment goes from one day to another(11 Pm 12/18/2007 to 4 AM 12/19/2007) and some picks up the date 12/18/2007 it will show the whole appointment form 12/18 to 12/19. 11 pm to 4 am and that is fine.
if someone picks up 12/19/2007 i only want to show that there is an appointment from 12 am to 4 am.
With my SQL it works for the first date 12/18/2007 but if if i pick 12/19/2007 it also shows the whole appointment. i only need to show the appointment for that day only 12 am - 4 am.
One of your problem in your where clause
You put down Where Schedule.Start_Date = '12/18/2007' OR Schedule.End_date='12/19/2009'
The start_date and end_date is the time the user supposes to pick it up but you did not indicate what was the time at that moment.
WHERE BETWEEN CONVERT(VARCHAR(20), Schedule.StartDate,
December 18, 2007 at 11:49 am
Sorry click the submit button too fast ......
WHERE CONVERT(VARCHAR(20), GETDATE(), 120) BETWEEN
CONVERT(VARCHAR(20), Schedule_info.Starting_time, 120) AND
CONVERT(VARCHAR(20), Schedule_info.Ending_time, 120)
CONVERT with 120 the date will become
yyyy-mm-dd hh:mi:ss(24h)
This way you can check the hour too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply