Sql Query (Comparing Time) - 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 2005 Forums

Transact-SQL (2005)

Sql Query (Comparing Time)

Author

Topic

HeatherMcVay
Starting Member

2 Posts

Posted - 2009-01-29 : 17:59:10

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

300 Posts

Posted - 2009-01-29 : 18:45:00

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.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-29 : 18:48:01

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

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-29 : 18:55:20

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.

HeatherMcVay
Starting Member

2 Posts

Posted - 2009-01-29 : 22:33:27

I never knew about the "BETWEEN" statement....
Thanks!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-30 : 10:54:07

You're welcome :)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources