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

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: Last updated:
Related Questions
1

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    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.
    85,885 pointsBadges:
    report
  • Darraca
    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
    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
    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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: