2 dates in a SQL stored proc - 7 days prior and 30 days in future

James Murphy
James Murphy used Ask the Experts™
on
Hi,

I have a new for 2 dates in my SQL stored procedure.  
1) 7 Days prior to today
2) 30 days in the future from today.

I am using these, but they don't seem to be working:
      DECLARE @7daysago DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()) -7, DATEADD(MONTH, 1, GETDATE())));
      DECLARE @30daysinfuture DATE = CONVERT(DATE, DATEADD(DAY, -DAY(GETDATE()) +30, DATEADD(MONTH, 1, GETDATE())));

Could anyone please point me in the right direction?

cheers
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
This seems to work for me:
select getdate() as today, getdate()-7 as seven_days_ago, getdate()+30 as thirty_days_from_now

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=951dc5b370207b87b35aa3f43c257c25

Author

Commented:
that is perfect.  Many thanks!
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help.

If you want a slightly different way that is probably more mainstream:
select getdate() as today, dateadd(day,-7,getdate()) as seven_days_ago, dateadd(day,30,getdate()) as thirty_days_from_now
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
be careful using getdate() because it is actually the current date AND time, you can use cast(getdate() as date) to set the time to 00:00:00

select
  cast(getdate() as date) as today
, cast(getdate() as date) -7  as seven_days_ago
, cast(getdate() as date) +30 as thirty_days_from_now

Using integers for addition isn't common in TSQL coding, instead  using dateadd() is more common, i.e.

select
  cast(getdate() as date) as today
, dateadd(day,-7,cast(getdate() as date) ) as seven_days_ago
, dateadd(day,30,cast(getdate() as date) ) as thirty_days_from_now

These will also work:

 CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))
 CONVERT(DATE, DATEADD(DAY, +30, GETDATE()))

[edit]
looking back at your originals it seems that you felt it necessary to alter the day and the month - individually - when using DATEADD(). Hopefully you can see that this isn't needed, dateadd() will accurately add or deduct the number of time units from a parameter datetime value.

If in doubt on how to use a function, books online will help: e.g.
https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial