-1

I am trying to get first date & last date for next 12 months from the date the report/query is run.I was able to get next 12months by using below query but not with first and last dates. Any suggestions on how to do this are appreciated.

SELECT STR(MONTH(DATEADD(mm,Number,GETDATE())),2)+'/'
      + STR(YEAR(DATEADD(mm,Number,GETDATE())),4),DATEPART(MM,'january '+'01 1900')
   FROM Master.dbo.spt_values
  WHERE NAME IS NULL
    AND Number BETWEEN 0 AND 11

But results it gives are:

enter image description here

But what I expect:

enter image description here

Programmermid
  • 588
  • 3
  • 9
  • 30
  • Possible duplicate of [Create list with first and last day of month for given period](https://stackoverflow.com/questions/21583877/create-list-with-first-and-last-day-of-month-for-given-period) – Tab Alleman May 10 '18 at 17:32
  • @TabAlleman .. the linked question is for Postgres. – Vamsi Prabhala May 10 '18 at 17:33

2 Answers2

0

Assuming EOMONTH function is supported, use 

SELECT dateadd(day,1,dateadd(month,number-1,EOMONTH(getdate()))) as mth_start,
dateadd(month,number,EOMONTH(getdate())) as mth_end
FROM Master.dbo.spt_values
WHERE NAME IS NULL
AND Number BETWEEN 0 AND 11
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
0

Try this query

   SELECT 
CONVERT(datetime,  STR(MONTH(DATEADD(mm,Number,GETDATE())),2)+'/01/'+ STR(YEAR(DATEADD(mm,Number,GETDATE())),4)) firstdate,
dateadd(day,-1,CONVERT(datetime, STR(MONTH(DATEADD(mm,Number+1,GETDATE())),2)+'/01/'+ STR(YEAR(DATEADD(mm,Number+1,GETDATE())),4))) lastdate
FROM Master.dbo.spt_values
WHERE NAME IS NULL
AND Number BETWEEN 0 AND 11

to fetch last date of month, we can remove one day from first date of next month.

Krupa
  • 457
  • 3
  • 14