如何使用 SQL Server 从当前日期减去 30 天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27247770/
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 to subtract 30 days from the current date using SQL Server
提问by Madpop
I am unable subtract 30 days from the current date and I am a newbie to SQL Server.
我无法从当前日期减去 30 天，而且我是 SQL Server 的新手。
This is the data in my column
这是我专栏中的数据
date
------------------------------
Fri, 14 Nov 2014 23:03:35 GMT
Mon, 03 Nov 2014 15:18:00 GMT
Tue, 11 Nov 2014 01:24:47 GMT
Thu, 06 Nov 2014 19:13:47 GMT
Tue, 04 Nov 2014 12:37:06 GMT
Fri, 1 Nov 2014 00:33:00 GMT
Sat, 5 Nov 2014 01:06:00 GMT
Sun, 16 Nov 2014 06:37:12 GMT
For creating the above column I used varchar(50)and now my problem is I want to display the dates for past 15-20 days from the date column can any one help with this issue ?
update [ how can i display last 7 days dates in order
为了创建我使用的上述列varchar(50)，现在我的问题是我想从日期列显示过去 15-20 天的日期，有人可以帮助解决这个问题吗？更新 [我如何按顺序显示过去 7 天的日期
回答by HoneyBadger
You can convert it to datetime, and then use DATEADD(DAY, -30, date).
您可以将其转换为datetime，然后使用DATEADD(DAY, -30, date).
See here.
见这里。
edit
编辑
I suspect many people are finding this question because they want to substract from current date (as is the title of the question, but not what OP intended). The comment of munyul below answers that question more specifically. Since comments are considered ethereal (may be deleted at any given point), I'll repeat it here:
我怀疑很多人发现这个问题是因为他们想从当前日期中减去（正如问题的标题，但不是 OP 的意图）。下面 munyul 的评论更具体地回答了这个问题。由于评论被认为是空灵的（可以在任何给定点删除），我将在这里重复：
DATEADD(DAY, -30, GETDATE())
回答by Veera
TRY THIS:
尝试这个：
Cast your VARCHAR value to DATETIME and add -30 for subtraction. Also, In sql-server the format Fri, 14 Nov 2014 23:03:35 GMTwas not converted to DATETIME. Try substring for it:
将您的 VARCHAR 值转换为 DATETIME 并添加 -30 进行减法。此外，在 sql-server 中，格式Fri, 14 Nov 2014 23:03:35 GMT未转换为 DATETIME。为它尝试子字符串：
SELECT DATEADD(dd, -30,
CAST(SUBSTRING ('Fri, 14 Nov 2014 23:03:35 GMT', 6, 21)
AS DATETIME))
回答by Muhammad Awais
Try this:
尝试这个：
SELECT GETDATE(), 'Today'
UNION ALL
SELECT DATEADD(DAY, 10, GETDATE()), '10 Days Later'
UNION ALL
SELECT DATEADD(DAY, –10, GETDATE()), '10 Days Earlier'
UNION ALL
SELECT DATEADD(MONTH, 1, GETDATE()), 'Next Month'
UNION ALL
SELECT DATEADD(MONTH, –1, GETDATE()), 'Previous Month'
UNION ALL
SELECT DATEADD(YEAR, 1, GETDATE()), 'Next Year'
UNION ALL
SELECT DATEADD(YEAR, –1, GETDATE()), 'Previous Year'
Result Set:
结果集：
———————– —————
2011-05-20 21:11:42.390 Today
2011-05-30 21:11:42.390 10 Days Later
2011-05-10 21:11:42.390 10 Days Earlier
2011-06-20 21:11:42.390 Next Month
2011-04-20 21:11:42.390 Previous Month
2012-05-20 21:11:42.390 Next Year
2010-05-20 21:11:42.390 Previous Year
回答by i486
SELECT DATEADD(day,-30,date) AS before30d
FROM...
But it is strongly recommended to keep date in datetime column, not varchar.
但强烈建议将日期保留在 datetime 列中，而不是 varchar。
