date time quetion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

date time quetion

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-11-16 : 11:17:00

I have one table in which i store the starting time and ending time of an appointment. The table is called schedule_info.
I want to enter appointment in the table at an 15 min interval. for example there is one appointment
start_time= 2007-11-14 10:00:00.000
end_time = 2007-11-14 11:00:00.000
so it is from 10 am to 11 am.

If some one tries to make an appointment from 11 am(end _time) i want to throw an error that u can make appointmnet from 11 :15 not 11.
So there needs to be gap of 15 min on every appointment.

This is the sql for geting all the appointments for that day

select * from schedule_info where _date='11/15/2207'

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] NULL ,
[Ending_Time] [datetime] 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 11:22:01

you mean there must be a 15 mins gap between 2 appointment ? You can have one appointment start immediately after the other ?

KH
[spoiler]Time is always against us[/spoiler]

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 11:23:59

by through an error do you mean add a table constraint? or do you what to through an error from insert/update procs? are NULL values actually ever entered in the Starting_Time field?

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-11-16 : 11:33:00

There should be a gap of 15 min between appointments
example first appointment starts at

4 am to 5 am

the next appointment should always start at
5: 15 am to 6:am should not start at 5:am

The next one should start at
6:15 Am to 10 AM not at 6 Am

in my code when some 1 inserts a appointment in the scheduleinfo table i check if the appointment exist.
here is the SQL for that
SELECT title FROM schedule_info WHERE (serverid=@serverid) AND _date=@_Date and (starting_time < @ending_time) AND (ending_time > @starting_time)

I also need to check for the Gap of min between the appointments.
The starting and ending time are not always nulls.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 11:36:17

use dateadd() to subtract 15 minutes from your input start time and add 15 mins to your end time and use it to check against existing records

KH
[spoiler]Time is always against us[/spoiler]

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 11:42:10

if these inserts happen frequently you will want something with better performance. if not this may be a starting point for you...

select title from Schedule_Info
where convert(varchar(13), Starting_Time, 121) = convert(varchar(13), @Starting_Time, 121)
and (datepart(mi, Starting_Time) % 15) = (datepart(mi, @Starting_Time) % 15)

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 11:48:25

khtan suggestion is better...
select title from Schedule_Info
where Ending_Time > dateadd(mi, -15, @Starting_Time)
or Starting_Time < dateadd(mi, -15, @Ending_Time)

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-11-16 : 12:45:12

Kind of not working

example data is Date is 11/15/2007
id=1
starting_time= 11/15/2007 2:00:00 AM
ending_time = 11/15/2007 3:00:00 AM

id 2
starting_time= 11/15/2007 4:00:00 AM
ending_time = 11/15/2007 5:00:00 AM

Here is the sql when i run this i don't get any records

DECLARE @STARTing_time DATETIME
DECLARE @ENDing_time DATETIME
SET @STARTing_time = '11/15/2007 3:00:00 AM'
SET @ENDing_time = '11/15/2007 3:30:00 AM'
select * from Schedule_Info
where Ending_Time > dateadd(mi, -15, @Starting_Time)
and Starting_Time < dateadd(mi, -15, @Ending_Time) and serverid='1' AND _date='11/15/2007'

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-11-16 : 13:27:55

It works when i select from the ending time
example if the appointment is from 3 am to 4 am and if i select from 4am to 5 am then it works.

if i select from 2 am to 3 am then it doesn't work.

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-11-19 : 12:27:22

I think i figured it out. Thanks for ur help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources