简单的 DateTime sql 查询

如何在一定范围内查询 DateTime 数据库字段？

我正在使用 SQLSERVER2005

错误代码如下

SELECT *
FROM TABLENAME
WHERE DateTime >= 12/04/2011 12:00:00 AM
AND DateTime <= 25/05/2011 3:53:04 AM

注意，我需要在一定的时间范围内获取行。例如，10分钟的时间范围。

目前 SQL 返回的语法在’12’附近不正确。”

584234 次浏览
小开
最佳答案

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.

SELECT *
FROM TABLENAME
WHERE
DateTime >= '2011-04-12T00:00:00.000' AND
DateTime <= '2011-05-25T03:53:04.000'
小开

You need quotes around the string you're trying to pass off as a date, and you can also use BETWEEN here:

 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:

Sql Server string to date conversion

小开 
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.

小开

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

  1. 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.

  2. 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).

小开 
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
小开

This has worked for me in both SQL Server 2005 and 2008:

SELECT * from TABLE
WHERE FIELDNAME > {ts '2013-02-01 15:00:00.001'}
AND FIELDNAME < {ts '2013-08-05 00:00:00.000'}
小开

You can execute below code

SELECT Time FROM [TableName] where DATEPART(YYYY,[Time])='2018' and DATEPART(MM,[Time])='06' and DATEPART(DD,[Time])='14
小开

if you have a type of datetime and you want to check between dates only ,,,use cast to select between two dates ....

example...

... where cast( Datetime as date) >= cast( Datetime as date) AND cast( Datetime as date) <= cast( Datetime as date)


提交答案