Reputation: 177
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
Upvotes: 11
Views: 89318
Reputation: 32760
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)
Upvotes: 29
Reputation: 5967
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.M Edit: I have checked this against MS SQL server 2012. Also it will work with datetime format.
Upvotes: 1