Selecting all the data using time greater than 4pm

It's hard to read your question, but assuming you really are using a datetime data type, you can use datepart to find any dates with a time greater than 4 PM:

WHERE datepart(hh, YourDate) > 16

Since you now need minutes as well, if you want records after 4:45 PM, you can cast your date to a time like this:

SQL Server 2000/2005

SELECT Yield, [Date], ProductType, Direct FROM MIAC_CCYX WHERE cast(convert(char(8), [Date], 108) as datetime) > cast('16:45' as datetime)

Essentially you cast the date using convert's Date and Time styles to convert the date to a time string, then convert back to a datetime for comparison against your desired time.

SQL Server 2008+

SELECT Yield, [Date], ProductType, Direct FROM MIAC_CCYX WHERE CAST([Date] as time) > CAST('16:45' as time)

This will work whatever your date is. This will not compare the dates. Rather, Datepart() will extract the hour element from datetime and comapre with your given time (e.g. 4 P.M. in your case)

SELECT * from <table> where DATEPART(hh, ModifiedDate) >= 16

I am assuming ModifiedDate as column name. this will return data from 4 P.M. to 11:59:59 P.MEdit: I have checked this against MS SQL server 2012. Also it will work with datetime format.