SQL的日期条件 [英] Date criteria for 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''

这篇关于SQL的日期条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！