SQL How to split out the Time from a DateTime col then use that in a WHERE Clause

Darraca

290 pts.

Tags:

SQL

SQL Server

Hi, I have an SQL Server database that has a table containing 3 fields that I need to query. 1 field holds the date, and the other 2 hold the start and end times. What I was hoping to do was to split out the time only from the Start and End time fields and then use these times in the WHERE clause to find out if a selected range falls between these 2 times. Can anyone help me please? Kind Regards

Software/Hardware used:
SQL Server

Asked: January 4, 2012 11:40 AM Last updated: February 28, 2012 12:35 PM

Answer Wiki

Last updated: January 4, 2012 3:43 PM GMT

Darraca290 pts.
History
Contributors
- Ordered by most recent
- Darraca290 pts.

Thanks. We'll let you know when a new response is added.

The reason I needed the time splitting out was because I was working on an assignment to create a room booking project in vb.net. The database is stored in SQL Server and I had to use a table called Bookings which had the following 3 columns:
BookDate
StartTime
EndTime

I wanted to create a query that brought back any rooms that had been booked for a date contained in the BookDate column that fell between the time that is stored between the StartTime and EndTime columns. This is the reason why I was only interested in the Time part of the StartTime and EndTime columns.

I sorted this out by making sure that the date in the StartTime and EndTime columns was 1 Jan 1900, followed by the time. That way I could write the query as something similar to the following:

StartTime EndTime
1900-01-01 15:30:00 1900-01-01 16:00:00

WHERE StartTime >= ‘15:00:00′ AND EndTime <= ‘16:00:00′

Discuss This Question: 4 Replies

carlosdl Jan 4, 2012 2:15 PM GMT

I'm not sure to understand why you need to split the time to use it in the WHERE clause. Maybe these functions can help you: DATENAME, DATEPART. It would be a good idea posting some example data, with the corresponding expected results.

84,495 pointsBadges:

report
Darraca Jan 4, 2012 3:43 PM GMT

Hi, sorry I've managed to sort this now. The reason I needed the time splitting out was because I was working on an assignment to create a room booking project in vb.net. The database is stored in SQL Server and I had to use a table called Bookings which had the following 3 columns: BookDate StartTime EndTime I wanted to create a query that brought back any rooms that had been booked for a date contained in the BookDate column that fell between the time that is stored between the StartTime and EndTime columns. This is the reason why I was only interested in the Time part of the StartTime and EndTime columns. I sorted this out by making sure that the date in the StartTime and EndTime columns was 1 Jan 1900, followed by the time. That way I could write the query as something similar to the following: StartTime EndTime 1900-01-01 15:30:00 1900-01-01 16:00:00 WHERE StartTime >= '15:00:00' AND EndTime <= '16:00:00' Thanks anyway :)

290 pointsBadges:

report
TomLiotta Jan 5, 2012 2:06 AM GMT
WHERE StartTime >= ‘15:00:00′ AND EndTime <= ‘16:00:00′ If those are DateTime data types, can't you do this?
```
WHERE CONVERT(time(0),StartTime) >= ‘15:00:00′ AND CONVERT(time(0),EndTime) <= ‘16:00:00′
```
Or am I misunderstanding? Tom
125,585 pointsBadges:
report
Walrum Jan 24, 2012 0:33 AM GMT

I believe that SELECT DATEPART(HH, GETDATE()) the best answer. in other words following your sample: WHERE DATEPART(HH, StartTime )>= ‘15′ AND DATEPART(HH, EndTime) <= ‘16′

270 pointsBadges:

report