using date fields

[MartDawg]

I’m building a little scheduling web page, where the user can check out a component for a certain time period. The web page will allow them to select a component, a begin time a begin hour an end time and an end hour. The only stipulation is that the same componenet cannot be checked out for the same time period…So.

I have two tables.

Table1
1,Comp1
2,Comp2

Table2
User,beg_date,end_date,beg_hour,end_hour,Component(from Table1)

Anybody come up with a good SQL job that given a begin date, end date, begin hour, end hour and a component, it will check the tables above and make sure that component is not all ready checked out. Here’s what I have so far, I’m having trouble tying in the hour fields….

 

[mrdenny]

Why use to seperate fields in the database for the beg_date and beg_hour? Why not just use a single datetime field beg_date.

Then it's easier to do something like this to check:

if exists (select * from table2 where convert(datetime, user_entered_beg_date) between beg_date and end_date and component = user_entered_component)
BEGIN
    print 'already checked out'
END
ELSE
BEGIN
    print 'available'
END

I'm pretty sure that, that will work for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MartDawg]

Thanks Denny, what would "user entered datetime" be with hour included...

"3/8/2004 14"

Will that work, if the user entered 3/8/2004 and 2 o'clock?

 

[mrdenny]

Have your frontend convert how ever the user enteres it to '3/8/2004 14:00:00'. With the "convert(datetime, '3/8/2004 14:00:00')" the SQL Server will work nicly with the date and time this way.

Denny

--Anything is possible. All it takes is a little research. (Me)