SQL 减去整整一年
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4247060/
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
SQL Subtract exactly a year
提问by MyHeadHurts
First I want to thank everyone for helping me, it gave me a lot of ideas on how i should do this and I came up with my own method i just need help putting it into a query
首先我要感谢大家帮助我，它给了我很多关于我应该如何做到这一点的想法，我想出了自己的方法，我只需要帮助将其放入查询中
I want the user to input a date, then get the current year. subtract the two and then do the dateadd that everyone was posting. Is this possible and what is the best way to do it?
我希望用户输入日期，然后获取当前年份。减去两者，然后执行每个人都发布的日期添加。这是可能的，最好的方法是什么？
year(getdate())-@DYYYY=Y
?
dateadd(year,-Y,getdate())
回答by Philip Kelley
Use this:
用这个：
dateadd(year, -1, getdate())
回答by Justin Wignall
Based on your comment regarding hard coded year values, the use of
根据您对硬编码年份值的评论，使用
DATEDIFF(year,BOOKED,GETDATE())
to get the number of years since the date you're after should hopefully lead you in the direction you are after.
获得自您所追求的日期以来的年数应该有望引导您朝着您所追求的方向前进。
You will probably end up with something like:
你可能会得到类似的结果：
SELECT DATEADD(year, -DATEDIFF(year,BOOKED,GETDATE()), GETDATE())
Ok, it looks more like all you really want to do (I may be wrong, sorry if so) is to group the bookings by year.
好的，看起来更像是您真正想做的（我可能错了，如果是这样，抱歉）就是按年份对预订进行分组。
Will the result of the following help achieve that?
以下结果是否有助于实现这一目标？
SELECT SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0), Sum(APRICE) as Total, Sum(PARTY) as PAX
FROM DataWarehouse.dbo.B01Bookings AS B101Bookings
GROUP BY SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0)
As I said, this is a guess as to your goal, not necessarily an answer to your question.
正如我所说，这是对您目标的猜测，不一定是您问题的答案。
回答by John Hartsock
To subtract a year from a date simply use DATEADD() function
要从日期中减去一年，只需使用 DATEADD() 函数
SELECT DATEADD(year, -1, GETDATE())
Edited:
编辑：
SELECT SDESCR,DYYYY, Sum(APRICE) as Total, Sum(PARTY) as PAX
FROM DataWarehouse.dbo.B01Bookings AS B101Bookings
WHERE
(BOOKED <= Convert(int, Convert(datetime, Convert(varchar, DatePart(month, GETDATE())) + '/' + Convert(varchar, DatePart(day, GetDate())) + '/' + DYYYY))
Group By SDESCR,DYYYY
Order by DYYYY
Edited 2:
编辑 2：
I just ran this statement
我刚刚运行了这个语句
select Convert(datetime, Convert(varchar, DatePart(month, GETDATE())) + '/' + Convert(varchar, DatePart(day, GetDate())) + '/' + '2007')
Which runs fine. so my question is what is stored in the DYYYY Column? Does it always contain a valid year, can it contain Nulls?
哪个运行良好。所以我的问题是 DYYYY 列中存储了什么？它是否始终包含有效年份，是否可以包含空值？
回答by StingyHyman
OK... From your response to Joe Stefanelli, I think that part of what you are really trying to do is avoid having to rewrite the query each year. If that is the case, then you can create a numbers table. A simple and fast example would be like this...
好的...从您对 Joe Stefanelli 的回复来看，我认为您真正想做的部分是避免每年都必须重写查询。如果是这种情况，那么您可以创建一个数字表。一个简单而快速的例子是这样的......
SELECT TOP 3000
IDENTITY(INT,1,1) as N
INTO #Numbers
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Instant table with integers from 1 to 3000.
具有从 1 到 3000 的整数的即时表。
This allows you to join or subselect against the #Numbers table for your query. If you want to make a more limited range, then you can make a table with just the years you like (or a table valued function that will make the same table dynamically).
这允许您针对您的查询加入或子选择#Numbers 表。如果你想制作一个更有限的范围，那么你可以制作一个只包含你喜欢的年份的表格（或者一个可以动态制作同一个表格的表值函数）。
You could also take it a bit further and implement a table valued function that will return a two column result
您还可以更进一步并实现一个表值函数，该函数将返回一个两列结果
year offset
2010 0 --Produced from DATEPART(y,GETDATE())
2009 -1 --loop/set subtract 1
2008 -2 --repeat until you have enough...
This way, your where clause could read something like
这样，您的 where 子句可以读取类似
SELECT *
FROM yourTable, yourFunction
WHERE ((DYYYY = CAST(yourFunction.year as VARCHAR) AND (BOOKED <= DATEADD(yy, yourFunction.offset, GETDATE()))
Note that while the tv functions should save you a bit of maintenance programming each year, you may suffer some minor performance hits.
请注意，虽然电视功能每年应该为您节省一些维护编程，但您可能会遭受一些轻微的性能损失。
回答by Ivan
The simplest way to get the date 1 year ago is:
获取 1 年前日期的最简单方法是：
SELECT GETDATE() - 365
回答by Joe Stefanelli
select DATEADD(yy, -1, getdate())
