如何在 sql server 中按特定日期检索行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5611570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how i can retrieve rows by specific date in sql server?
提问by user700792
I'm working in project for by PHP and SQL Server.
我正在为 PHP 和 SQL Server 的项目工作。
The owner want from me design page show only users who register in same day
业主要我设计页面只显示当天注册的用户
i.e., if today 11-3-2011 i want show only all users who register in 11-3-2011
即，如果今天 11-3-2011 我只想显示所有在 11-3-2011 注册的用户
The table is:
表是：
id username date
1 john 1111
2 sara 1111
3 john 511
4 kreem 111
i make it by mysql
我用 mysql 做的
where DATE_ADD( items.created_date, INTERVAL 1 DAY ) > NOW()
this cable of code show data which only insert in same day thats mean if today 10-4-2011 it will show only data which insert in 10-4-2011 if i today 15-4-2011 and im dose not insert any thing it will not show any thing, how i can build code like this in sql server? hope to be my question clear and understand
这条代码电缆显示仅在同一天插入的数据，这意味着如果今天 10-4-2011 它将仅显示在 10-4-2011 插入的数据，如果我今天 15-4-2011 并且我没有插入任何东西不会显示任何东西，我如何在 sql server 中构建这样的代码？希望我的问题清楚明白
回答by abatishchev
select id, userName
from YourTable
where CAST(date AS DATE) = CAST(GETDATE() AS DATE)
Function GETDATE()returns the current date and time.
函数GETDATE()返回当前日期和时间。
CAST(column as TYPE)will threat DateTime as just Date to omit differences in Time
CAST(column as TYPE)将威胁 DateTime 作为 Date 以忽略时间差异
回答by Bhairab
For getting today's date record:
获取今天的日期记录：
select * from tablename where date column between '2011-10-25 00:00:00' And '2011-10-25 23:59:59'
回答by prabu
select * from yourtable where date = CONVERT(VARCHAR(20),GETDATE(),101)// hope this will be helpful to get current date value..
select * from yourtable where date = CONVERT(VARCHAR(20),GETDATE(),101)// 希望这有助于获取当前日期值..
回答by BlueMonkMN
This query compares only the date piece of today, ignoring the time. It works in MS SQL Server, I'm not sure about other implimentations:
此查询仅比较今天的日期，忽略时间。它适用于 MS SQL Server，我不确定其他实现：
select *
from YourTable
where convert(datetime, floor(convert(float, GETDATE()))) = [date]
回答by Mikael Eriksson
select *
from YourTable
where
[date] >= '20110311' and
[date] < '20110312'
If you want it for today (always) you can use this
如果你今天（总是）想要它，你可以使用它
select *
from YourTable
where
[date] >= dateadd(d, datediff(d, 0, getdate()), 0) and
[date] < dateadd(d, datediff(d, 0, getdate())+1, 0)
Last 10 days.
最近10天。
select *
from YourTable
where
[date] >= dateadd(d, datediff(d, 0, getdate())-9, 0)
Edit 1Query with sample data and result
使用示例数据和结果编辑 1 个查询
Table definition
表定义
create table users
(name varchar(20),
user_register_date datetime)
Data
数据
insert into users values ('user today', getdate())
insert into users values ('user yesterday', getdate()-1)
insert into users values ('user 9 days ago', getdate()-9)
insert into users values ('user 10 days ago', getdate()-10)
Query
询问
select *
from users
where user_register_date >= dateadd(d, datediff(d, 0, getdate())-9, 0)
Result
结果
name user_register_date
-------------------- -----------------------
user today 2011-04-14 08:29:28.407
user yesterday 2011-04-13 08:29:28.410
user 9 days ago 2011-04-05 08:29:28.410
回答by elvis
SELECT * from YourTable
WHERE cast(convert(varchar(10),yourdate,101) as datetime) =
cast(convert(varchar(10),[any day you want],101) as datetime)
Hope this helps. Just replace [any day you want] with a date or datetime value
希望这可以帮助。只需用日期或日期时间值替换 [您想要的任何一天]
The CAST function is equivalent to the CONVERT function, except convert allows for some formatting options. In my example, I simply trimmed off the time from the date.
CAST 函数等效于 CONVERT 函数，但 convert 允许使用某些格式选项。在我的示例中，我只是从日期中剪掉了时间。
