如何使用SQL Server从当前日期减去30天

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天的日期

4 个解决方案

#1

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())

#2

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 GMT was not converted to DATETIME. Try substring for it:

将VARCHAR值转换为DATETIME并为减法添加-30。另外，在sql-server中格式为Fri，2014年11月14日23:03:35 GMT未转换为DATETIME。尝试使用substring：

SELECT DATEADD(dd, -30, 
       CAST(SUBSTRING ('Fri, 14 Nov 2014 23:03:35 GMT', 6, 21) 
       AS DATETIME))

#3

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

#4

SELECT DATEADD(day,-30,date) AS before30d 
FROM...

But it is strongly recommended to keep date in datetime column, not varchar.

但强烈建议在datetime列中保留日期，而不是varchar。