How to subtract 30 days from the current date using SQL Server

I am unable subtract 30 days from the current date and I am a newbie to 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

You can convert it to datetime, and then use 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:

DATEADD(DAY, -30, GETDATE())

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

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:

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

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

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

How to subtract 30 days from the current date using SQL Server

Related

Recent Posts