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

Question

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

User · Answer

SELECT DATEADD day -30 date  AS before30d  FROM      But it is strongly recommended to keep date in datetime column  not varchar

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2012