SQL的日期条件

本文介绍了SQL的日期条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

您好，SQL Server 2008 R2 今天是星期五，我的大脑受伤了.我想查找昨天的所有记录，所以我有

Hello, SQL Server 2008 R2 It is Friday and my brain hurts. I want to find all records for yesterday so I have

SELECT * FROM myTable WHERE myDateTime = ''1/27/2011''

由于记录有时间，因此不会这样做.因此，我想做的事情类似

This will not do it as the records have the time. Thus what I would like is to do something like

WHERE CAST(myDateTime AS DATE) = ''1/27/2011''

，但这会破坏索引. 所以我需要使用类似

however this kills indexing. So do I need to use something like

WHERE myDateTime BETWEEN ''1/27/2011'' AND ''1/28/2011''

的东西吗 谢谢 djj

Thank you, djj

推荐答案

WHERE myDateTime >= ''1/27/2011'' and myDateTime < ''1/28/2011''

*考虑我们的讨论

*considering our discussion

WHERE (CONVERT(nvarchar, myDateTime, 101) = ''01/27/2011'')

如果您想使用单个日期进行操作，则可以执行以下操作 If you wanted to do it using a single date, you could do something like this select * FROM SomeTable WHERE CAST(FLOOR( CAST( YourField AS FLOAT ) )AS DATETIME) = '1/27/2011'

因此，从字段中删除时间，然后与某个值进行比较 更新:抱歉，我看到了您关于该杀害指标的帖子 否则，唯一的方法是使用BETWEEN.

So, strip out the time from the field and then compare to some value Update: sorry, I saw your post about that killing indexes Otherwise, the only way to do it is to use a BETWEEN.

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @StartDate = CAST(FLOOR( CAST( GETDATE-1 AS FLOAT ) )AS DATETIME) SET @EndDate = CAST(FLOOR( CAST( GETDATE AS FLOAT ) )AS DATETIME) select * FROM SomeTable WHERE YourField BETWEEN @StartDate AND @EndDate

有关: WHERE myDateTime >= ''1/27/2011''