SQL 我如何找到一年前这一天的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24335452/
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 do I find data from this day exactly one year ago?
提问by user3630473
Right now I have:
现在我有：
year(epe_curremploymentdate) = year(DATEADD(year, -1, SYSDATETIME()))
But that is giving me everything from last year. All I want is the data from today's date, one year ago.
但这给了我去年的一切。我想要的只是一年前今天日期的数据。
回答by Karthik Ganesan
It should be:
它应该是：
epe_curremploymentdate = DATEADD(year, -1, GETDATE())
Don't check for yearin your where clause.
不要在 where 子句中检查年份。
EDIT:
编辑：
Simple: use
简单：使用
cast(epe_curremploymentdate AS DATE) = cast(DATEADD(year, -1,
GETDATE()) AS DATE)
That is, if you are using SQL Server 2008 and above.
也就是说，如果您使用的是 SQL Server 2008 及更高版本。
回答by Dean
This should get you to where you need to go:
这应该能让你到达你需要去的地方：
Declare @StartDate datetime, @EndDate datetime
-- @StartDate is midnight on today's date, last year
set @StartDate = Convert(date, (DATEADD(year, -1, getdate())))
set @EndDate = DATEADD(Day, 1, @StartDate)
select *
from YourTable
where epe_curremploymentdate >= @StartDate
and epe_curremploymentdate < @EndDate
-- This where clause will get you everything that happened at any time
-- on today's date last year.
回答by Satwik Nadkarny
Date today:
今天的日期：
select getdate()
date one year ago:
一年前的日期：
select dateadd(year, -1, getdate())
Date one year and one day ago:
一年零一天前的日期：
select dateadd(d, -1 , dateadd(year, -1, getdate()))
UPDATE:
更新：
select *
from
epe_curremploymentdate = dateadd(year, -1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
This will give you a list of all employees who started working exactly from a year ago.
这将为您提供从一年前开始工作的所有员工的列表。
Hope this helps!!!
希望这可以帮助！！！
回答by Greg Vavoules
If you have other fields that show as datetime with the time part as 00:00:00.000, to do joins you can use the below
如果您有其他字段显示为日期时间，时间部分为 00:00:00.000，要进行连接，您可以使用以下内容
dateadd(yy,-1,datediff(d,0,getdate()))
回答by mehdi lotfi
epe_curremploymentdate = DATEADD(year, -1, GETDATE())
epe_curremploymentdate = DATEADD(year, -1, GETDATE())
