SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sql Query (Comparing Time)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HeatherMcVay
Starting Member

USA
2 Posts

Posted - 01/29/2009 :  17:59:10  Show Profile  Reply with Quote
How do you do a SQL Query for times on a particular date?

I built a little reservation system for picnic table reservations for a local park, and I want to reserve table "A" from 9am-11am on Saturday. So when I check my reservations table, I do have 3 columns, TABLE_NAME, FROM, TO and the from/to columns store the date in this format: 01/31/2009 10:00:00 am .......


My question is, how do I search that table to see if 9am-11am is good, when there is another reservation from 10am-12pm for that table?????



Skorch
Constraint Violating Yak Guru

USA
295 Posts

Posted - 01/29/2009 :  18:45:00  Show Profile  Reply with Quote
This will output any tables that are conflicting with your specified table name and time range:

CREATE TABLE #temp (ID INT, TABLE_NAME VARCHAR(25), [FROM] DATETIME, [TO] DATETIME)
INSERT INTO #temp values (1, 'tab1', '1/30/09 10:00:00', '1/30/09 11:59:59')
INSERT INTO #temp values (2, 'tab1', '1/30/09 12:00:00', '1/30/09 13:59:59')
INSERT INTO #temp values (3, 'tab1', '1/30/09 14:00:00', '1/30/09 15:59:59')
GO
DECLARE @TABLE VARCHAR(25), @FROM DATETIME, @TO DATETIME
SELECT @TABLE = 'tab1', @FROM = '1/30/09 09:00:00', @TO = '1/30/09 11:00:00'
SELECT ID, TABLE_NAME, [FROM], [TO]
FROM #temp
WHERE @TABLE = TABLE_NAME
AND (@FROM BETWEEN [FROM] AND [TO]
OR @TO BETWEEN [FROM] AND [TO])

Some days you're the dog, and some days you're the fire hydrant.

Edited by - Skorch on 01/29/2009 18:46:16
Go to Top of Page

revdnrdy
Posting Yak Master

USA
220 Posts

Posted - 01/29/2009 :  18:48:01  Show Profile  Reply with Quote
Hello;

I think for this case you will want to look at the DATEADD and DATEDIFF functions. Search this forum as it has several examples.

As a simple example here is how I check a table called summary to extract any records entered by user 394 or 400 in the last 2 days.

SELECT * from summary
where user_id in ('394','400')
and date_time > DATEADD(day, DATEDIFF(day, 0, GETDATE())-2,0)

For your query though you need extra logic to handle whether or not a time is available. You can use IF statements or CASE statements for that. IF no dates come back THEN it is available (for example).

r&r

Edited by - revdnrdy on 01/29/2009 18:53:56
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
295 Posts

Posted - 01/29/2009 :  18:55:20  Show Profile  Reply with Quote
I forgot to mention, my code assumes that your FROM and TO columns are of DATETIME datatype. If they're not, I would strongly suggest you alter your table and change their datatype to allow yourself to make calculations with the dates.

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page

HeatherMcVay
Starting Member

USA
2 Posts

Posted - 01/29/2009 :  22:33:27  Show Profile  Reply with Quote
I never knew about the "BETWEEN" statement....
Thanks!
Go to Top of Page

Skorch
Constraint Violating Yak Guru

USA
295 Posts

Posted - 01/30/2009 :  10:54:07  Show Profile  Reply with Quote
You're welcome :)

Some days you're the dog, and some days you're the fire hydrant.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000