Fantastic I'll give that a go.
-----Original Message-----
From: Bert Dawson [mailto:[EMAIL PROTECTED]]
Sent: 14 February 2001 10:09
To: Fusebox
Subject: RE: sql date functions
booked table:
boo_id boo_roo_id boo_date_start boo_date_end
1 11 1st Jan 2001 9th Jan 2001
2 12 2nd Jan 2001 4th Jan 2001
3 13 3rd Jan 2001 7th Jan 2001
4 14 1st Jan 2001 1st Jan 2001
5 14 5th Jan 2001 8th Jan 2001
rooms table:
roo_id roo_colour roo_bed
11 blue hard
12 pink soft
13 white water
14 gold wooden
15 grey n/a
rooms available between 2nd and 4th jan:
SELECT *
FROM rooms
WHERE NOT EXISTS (SELECT NULL
FROM booked
WHERE boo_roo_id = roo_id
AND boo_date_start <= '4 Jan 2001'
AND boo_end_date => '2 Jan 2001')
I think that should do it, though its still early so no guarantees....
Bert
ps obviously the dates should all be proper date objects/datatime columns
etc
> -----Original Message-----
> From: John McCosker [mailto:[EMAIL PROTECTED]]
> Sent: 14 February 2001 08:35
> To: Fusebox
> Subject: sql date functions
>
>
> Hi all, can anyone help me with this. I am building a hotel
> bookings site
> in coldfusion.
> If a room is booked from dateA to dateB I make a record in
> #Dateformat(dateA, dd-mm-yy)#, #Dateformat(dateB, dd-mm-yy)#
> and room, time
> etc.
>
> There does not seem to be a sql function in coldfusion that
> will allow me to
> determine if someone else trys to book the same room starting
> before DateA
> and ending between both dateA and dateB.
>
> Ok now I know I could build a second database table and do a
> 'select not
> in', but it would not be as effective I feel.
>
> Cheers
> Jonny
>
> Derry Northern Ireland
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
