SQL 使用大于 4pm 的时间选择所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13572335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting all the data using time greater than 4pm
提问by user1783998
I need to select the Data containing time > 4pm in datatimestampevery 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.
我需要datatimestamp在 SQL Server Management Studio Microsoft SQL Server 2005 - 9.00.4060.00 (X64) DB 表中每天选择包含时间 > 4pm 的数据，该表有两年的数据。做到这一点的最佳方法是什么？我的时间戳具有以下格式：DATETIME, '2005-10-13 16:00:00', 102. 我每天下午随机获取数据。我需要每天下午4点后获取数据。不止一天。
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
回答by LittleBobbyTables - Au Revtheitroad
It's hard to read your question, but assuming you really are using a datetimedata type, you can use datepartto find any dates with a time greater than 4 PM:
很难阅读您的问题，但假设您确实在使用datetime数据类型，则可以使用它datepart来查找时间大于下午 4 点的任何日期：
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:
由于您现在也需要分钟，如果您想要下午 4:45 之后的记录，您可以将日期转换为这样的时间：
SQL Server 2000/2005
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 stylesto convert the date to a time string, then convert back to a datetimefor comparison against your desired time.
本质上，您使用convert的 Date 和 Time 样式转换日期以将日期转换为时间字符串，然后转换回 adatetime以与所需时间进行比较。
SQL Server 2008+
SQL Server 2008+
SELECT Yield, [Date], ProductType, Direct
FROM MIAC_CCYX
WHERE CAST([Date] as time) > CAST('16:45' as time)
回答by SKJ
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)
无论您的日期是什么，这都将起作用。这不会比较日期。相反， Datepart() 将从日期时间中提取小时元素并与您的给定时间（例如，您的情况下为下午 4 点）
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.
我假设 ModifiedDate 作为列名。这将返回从下午 4 点到晚上 11:59:59 的数据编辑：我已经针对 MS SQL Server 2012 进行了检查。它也适用于日期时间格式。
