Home > IT Answers > Development > SQL How to split out the Time from a DateTime col then use that in a WHERE Clause
Darraca
260 pts.
 SQL How to split out the Time from a DateTime col then use that in a WHERE Clause
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
UPDATED: February 28, 2012  12:35 PM
RELATED QUESTIONS
Answer Wiki:
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′
Last Wiki Answer Submitted:  January 4, 2012  3:43 pm  by  Darraca   260 pts.
All Answer Wiki Contributors:  Darraca   260 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

carlosdl
 63,535 pts.
 

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 :)

Darraca
 260 pts.
 

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

TomLiotta
 108,215 pts.
 

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′

Walrum
 270 pts.