Question

我有一个表格,有一栏的时日。我想提出一个问题,选择该日所有各行。基本情况

SELECT * FROM Table
WHERE [timeStamp] =  02-15-2003

但是,只有回归者,如果[当时的Stamp]是02-15-2003 00:00:000,但我真的想从那一天的某个时候赶走。

Answer 1

如果你有指数,你会想做一些没有阻止指数使用的东西:

SELECT *
FROM Table 
WHERE [timeStamp] >=  20030215 
      AND [timeStamp] <  20030216

You can do a truncation operation on the [timeStamp] column to get rid of any time part (implementation dependent), but this can potentially hurt the execution plan. Unfortunately, you really have to look at the execution plan to see this, because sometimes the optimizer is clever about some functions and sometimes it isn t.

Answer 2

如果你在2008年5月上台,你就应该到下台。

select * from [Table]
where cast([timeStamp] as date) =  02-15-2003

消除服务器中时间部分的最佳做法

——————

评论者应在2012年回过来描述为什么这不是最佳解决办法:sargability。改变WHERE条款中的数据类型,虽然这是最简单的解决办法,但对指数使用的影响是,经过约束的检索不会。

Answer 3

in sql server 2008 + :

  SELECT * FROM Table
        WHERE cast( [timeStamp] as date)  =  02-15-2003

或

仅ZERO时间段:(2005年+)

   SELECT * FROM Table
    WHERE DateAdd(day, DateDiff(day, 0, [timeStamp]), 0)  =  02-15-2003

Answer 4

《2014年行政法》是完美的:

SELECT [YourDateColumn] FROM [YourTable] WHERE(DATEPART(dd,[YourDateColumn]) =  29 )

雄厚的银有一些绩效问题。

Answer 5

这样做。页: 1

SELECT * FROM Table WHERE convert(date,[timestamp]) =  2003-02-15

应于规定日期交还所有各行。

Answer 6

我将设立一个接受“启动日期”和“日期”的储存程序。

在这种情况下,起始日期和结束日期可以相同。

这确保了从12:01AM到11:59PM的所有各行都归还。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE  TestBetweenDates
    -- Add the parameters for the stored procedure here
    @StartDate DateTime = 0, 
    @EndDate DateTime = 0
AS
BEGIN

    SET NOCOUNT ON;

        SET @StartDate = cast(Convert(varchar(10), DateAdd(d, -6, @StartDate ), 101) +   12:01 AM  as datetime)

        SET @EndDate = cast(Convert(varchar(10), DateAdd(d, -0, @EndDate), 101) +   11:59 PM  as datetime)

SELECT * FROM Table
WHERE ([timeStamp] BETWEEN @StartDate AND @EndDate) 


END
GO

友情链接