Selecting records based on current time falling between ProgramStart and ProgramEnd

I need to select all records from my SQL Server database that falls between a certain hour. Let me give you an example:

Current time is 9:33 am and you have numerous Program records in the database that have an ProgramStart and ProgramEnd. An example of the dates might be:

 | ProgramStart             | ProgramEnd              | ClientID |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 09:00:00.000  | 1900-01-01 10:00:00.000 | 5        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 11:00:00.000  | 1900-01-01 13:00:00.000 | 4        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 12:00:00.000  | 1900-01-01 14:00:00.000 | 5        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 11:00:00.000  | 1900-01-01 13:00:00.000 | 6        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 16:00:00.000  | 1900-01-01 17:00:00.000 | 7        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 16:00:00.000  | 1900-01-01 17:00:00.000 | 5        |
 +--------------------------+-------------------------+----------+
 | 1900-01-01 17:00:00.000  | 1900-01-01 18:00:00.000 | 4        |
 +--------------------------+-------------------------+----------+

I want to extract all records for client 5 and between the current time which is 9:33am

How does one do that with SQL. I tried this, but it doesn't work:

 SELECT CAST('1900-01-01 0开发者_高级运维9:33:00.000' AS datetime) AS TimeNow, * FROM Programs
 WHERE ClientID = 5
 AND TimeNow BETWEEN '1900-01-01 09:00:00.000' AND '1900-01-01 10:00:00.000'

any suggestions would be greatly appreciated.

Many thanks, Paul

---

select *
    from Programs
    where ClientId = 5
        and DATEADD(Day, 0-DATEDIFF(Day, 0, GetDate()), GetDate()) between ProgramStart and ProgramEnd

---

This but will remove the date component from GETDATE:

DATEADD(day, -DATEDIFF(DAY, 0, GETDATE()), GETDATE())

Plugging this into the query

SELECT
     *
FROM
     Programs
WHERE
     ClientID = 5
     AND
     DATEADD(day, -DATEDIFF(DAY, 0, GETDATE()), GETDATE())
                 BETWEEN ProgramStart AND ProgramEnd

On SQL Server 2008+ you have the time datatype too. However, the triple CAST one would invalidate indexes on ProgramStart and ProgramEnd so it'd worth playing with something like the 2nd one

CAST(GETDATE() AS time) BETWEEN CAST(ProgramStart AS time) AND CAST(ProgramEnd AS time)
CAST(CAST(GETDATE() AS time) AS smalldatetime) BETWEEN ProgramStart AND ProgramEnd

---

Try:

SELECT *
FROM Programs
WHERE ClientId = 5
AND GETDATE() BETWEEN ProgramStart AND ProgramEnd

Update I didn't notice the lack of a date component in the data. You should look at gbn's answer to scrape the date out after calling GetDate(). I won't just copy his code here because that seems wrong. :)