2 dates in a SQL stored proc - 7 days prior and 30 days in future
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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
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
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(getdat
, dateadd(day,30,cast(getdat
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
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(getdat
, dateadd(day,30,cast(getdat
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial