获取今天创建的所有行 [英] Getting all rows created today

问题描述

我无法获取今天创建的所有行.我使用了多个函数，如 getdate()、Cast、Convert 等，但都是徒劳的.

I am unable to get all the rows created today. I have used multiple functions like getdate(), Cast, Convert, etc. but all in vain.

这是我的基本查询:

SELECT timeId
FROM table_roaster_time_table
WHERE (user_id = @user_id) AND (DATEDIFF(d, date, GETDATE()) = 0)

我想从表 table_roaster_time_table 中获取 timeId，其中 userid 将被提供，日期是今天.

I want to get the timeId from the table table_roaster_time_table where userid will be provided and the date is today.

我该怎么做?

推荐答案

为了保持任何机会使用 [date] 列上的索引(即使今天不存在，将来可能会)，请尝试:

In order to keep any chance of using an index on the [date] column (even if one doesn't exist today, it may in the future), try:

AND [date] >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
AND [date] <  DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));

如果您使用的是 SQL Server 2008 或更高版本，您可以执行类似的操作来缩短代码，但仍然使用 [date] 上的索引(如果存在):

If you're using SQL Server 2008 or better, you can do something like this to shorten the code but still make use of an index on [date] if one exists:

AND CONVERT(DATE, [date]) = CONVERT(DATE, CURRENT_TIMESTAMP);

编辑

既然您似乎对为什么 3/6/2012 是 3 月 6 日而不是 6 月 3 日感到困惑，我还建议您不要手动插入像 '3/6 这样的模糊日期文字/2012' 进入数据库，您将列设为默认值，例如:

Since you seem to be confused why 3/6/2012 is March 6th and not June 3rd, I might also suggest that instead of manually inserting ambiguous date literals like '3/6/2012' into the database, you make the column a default such as:

ALTER TABLE dbo.table_roaster_time_table
  ALTER COLUMN [date] DATETIME NOT NULL;

ALTER TABLE dbo.table_roaster_time_table
  ADD CONSTRAINT df_date DEFAULT (CURRENT_TIMESTAMP)
  FOR [date];

如果您要插入日期文字，那么至少使用安全且明确的格式，例如 YYYYMMDD:

If you're going to insert date literals then at least use a safe and unambiguous format, such as YYYYMMDD:

INSERT dbo.table_roaster_time_table([date]) VALUES('20120603');

现在没有混淆了.

这篇关于获取今天创建的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！