简单的日期时间 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6119369/
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
Simple DateTime sql query
提问by ove
How do I query DateTime database field within a certain range?
如何查询一定范围内的DateTime数据库字段？
I am using SQL SERVER 2005
我正在使用 SQL SERVER 2005
Error code below
错误代码如下
SELECT *
FROM TABLENAME
WHERE DateTime >= 12/04/2011 12:00:00 AM
AND DateTime <= 25/05/2011 3:53:04 AM
Note that I need to get rows within a certain time range. Example, 10 mins time range.
请注意，我需要在特定时间范围内获取行。例如，10 分钟的时间范围。
Currently SQL return with Incorrect syntax near '12'."
当前 SQL 返回的语法不正确，接近 '12'。”
回答by Alex Aza
You missed single quote sign:
你错过了单引号：
SELECT *
FROM TABLENAME
WHERE DateTime >= '12/04/2011 12:00:00 AM' AND DateTime <= '25/05/2011 3:53:04 AM'
Also, it is recommended to use ISO8601 format YYYY-MM-DDThh:mm:ss.nnn[ Z ], as this one will not depend on your server's local culture.
此外，建议使用 ISO8601 格式 YYYY-MM-DDThh:mm:ss.nnn[ Z ]，因为这不会取决于您服务器的本地文化。
SELECT *
FROM TABLENAME
WHERE
DateTime >= '2011-04-12T00:00:00.000' AND
DateTime <= '2011-05-25T03:53:04.000'
回答by Milimetric
You need quotes around the string you're trying to pass off as a date, and you can also use BETWEEN here:
您需要在尝试作为日期传递的字符串周围加上引号，您也可以在此处使用 BETWEEN：
SELECT *
FROM TABLENAME
WHERE DateTime BETWEEN '04/12/2011 12:00:00 AM' AND '05/25/2011 3:53:04 AM'
See answer to the following question for examples on how to explicitly convert strings to dates while specifying the format:
有关如何在指定格式时将字符串显式转换为日期的示例，请参阅以下问题的答案：
回答by Jamaria
This has worked for me in both SQL Server 2005 and 2008:
这在 SQL Server 2005 和 2008 中都对我有用：
SELECT * from TABLE
WHERE FIELDNAME > {ts '2013-02-01 15:00:00.001'}
AND FIELDNAME < {ts '2013-08-05 00:00:00.000'}
回答by Abdul Rafey
You can execute below code
您可以执行以下代码
SELECT Time FROM [TableName] where DATEPART(YYYY,[Time])='2018' and DATEPART(MM,[Time])='06' and DATEPART(DD,[Time])='14
回答by clyc
SELECT *
FROM TABLENAME
WHERE [DateTime] >= '2011-04-12 12:00:00 AM'
AND [DateTime] <= '2011-05-25 3:35:04 AM'
If this doesn't work, please script out your table and post it here. this will help us get you the correct answer quickly.
如果这不起作用，请编写您的表格并在此处发布。这将帮助我们快速为您提供正确答案。
回答by ErikE
Others have already said that date literals in SQL Server require being surrounded with single quotes, but I wanted to add that you can solve your month/day mixup problem two ways (that is, the problem where 25 is seen as the month and 5 the day) :
其他人已经说过 SQL Server 中的日期文字需要用单引号括起来，但我想补充一点，您可以通过两种方式解决月/日混淆问题（即，将 25 视为月份，将 5 视为月份的问题）日） ：
Use an explicit
Convert(datetime, 'datevalue', style)where style is one of the numeric style codes, see Cast and Convert. The style parameter isn't just for converting dates to strings but also for determining how strings are parsed to dates.Use a region-independent format for dates stored as strings. The one I use is 'yyyymmdd hh:mm:ss', or consider ISO format,
yyyy-mm-ddThh:mi:ss.mmm. Based on experimentation, there are NO other language-invariant format string. (Though I think you can include time zone at the end, see the above link).
使用显式
Convert(datetime, 'datevalue', style)，其中 style 是数字样式代码之一，请参阅Cast 和 Convert。style 参数不仅用于将日期转换为字符串，还用于确定如何将字符串解析为日期。对存储为字符串的日期使用独立于区域的格式。我使用的是'yyyymmdd hh:mm:ss'，或者考虑ISO格式，
yyyy-mm-ddThh:mi:ss.mmm. 根据实验，没有其他语言不变的格式字符串。（虽然我认为您可以在最后包含时区，请参阅上面的链接）。
回答by Hyman.mike.info
select getdate()
O/P
----
2011-05-25 17:29:44.763
select convert(varchar(30),getdate(),131) >= '12/04/2011 12:00:00 AM'
O/P
---
22/06/1432 5:29:44:763PM
回答by Dan
Open up the Access File you are trying to export SQL data to. Delete any Queries that are there. Everytime you run SQL Server Import wizard, even if it fails, it creates a Query in the Access DB that has to be deleted before you can run the SQL export Wizard again.
打开您尝试将 SQL 数据导出到的 Access 文件。删除那里的任何查询。每次运行 SQL Server 导入向导时，即使它失败，它也会在 Access DB 中创建一个查询，必须先删除该查询，然后才能再次运行 SQL 导出向导。
