Selecting all the data using time greater than 4pm

Question

I need to select the Data containing time > 4pm in `datatimestamp` every day in SQL Server Management Studio Microsoft SQL Server 2005 - 9.00.4060.00 (X64) DB table which has two years of data. What's the best way to do this? My time stamp has following format:`DATETIME, '2005-10-13 16:00:00', 102`. I have data at random times every afternoon. I need to get the data after 4pm for every day. Not just for one day. For example i tried for one day like this: ``` SELECT Yield, Date, ProductType, Direct FROM MIAC_CCYX WHERE (Date < CONVERT(DATETIME, '2005-10-13 16:00:00', 102)) Thanks for help ```

Accepted Answer

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

Selecting all the data using time greater than 4pm

Solution

Problem