Date Variables

Hello all.

I have a stored procedure that I need to schedule to run on the 30th of the month.

The SP needs to have a Start Date of  Previous Month/15/YYYY and End Date Current Month/14/YYYY.  
I was thinking I could just use MONTH(DateAdd(MM,1, getDate()) and then append the rest of the date here.

So for example last month I need to have the date range to be 11/15/2017 to 12/14/2017.  This month I need the range to be 12/15/2017 to 01/14/2018.  

How do I get the years to change when appropriate?

Using MS SQL 2014

Thanks,
Rodger
LVL 4
RodgerSystems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Try the following to get Start/End date
select convert(datetime,substring(convert(varchar,dateadd(month,-1,getdate()),112),1,6)+'15',112) Start_date,convert(datetime,substring(convert(varchar,getdate(),112),1,6)+'14',112) End_date

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RodgerSystems AnalystAuthor Commented:
That worked thank you.  I will look at how you did that so I know for future use.  Thanks again.
0
Mark WillsTopic AdvisorCommented:
I know this is solved, but to keep DATE / DATETIME datatypes,  there was a new function in SQL 2012 EOMONTH()

SELECT DATEADD(DAY, 15, EOMONTH(getdate(), - 2)) AS START_PERIOD
      ,DATEADD(DAY, 14, EOMONTH(getdate(), - 1)) AS END_PERIOD

Open in new window


Check it out :)
0
RodgerSystems AnalystAuthor Commented:
Great I will keep that in mind.  I did see EOMonth just never used it.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.