How to select last 5 month name between specific date in SQL

Question

0.00/5 (No votes)

See more:

Hello I wanted to get the list of last 5 month name from current date. AND Using the below sql query i am get it but here i wanted to get the list in between the specific date how to get the list of last 5 month between specific date.

What I have tried:

;with cte as
(select 0 as num
union all
select num+1 from cte where num<4)
select month(dates) as MonthID,datename(month,dates) as MonthName,year(dates) Year,datename(m,dates)+' '+cast(datepart(yyyy,dates) as varchar) as MonthYearName
from (select dateadd(mm,-num,DATEADD(dd,1,eomonth(getdate(),-1))) as dates from cte) A

Posted 28-May-18 20:51pm

Member 13366575

Updated 30-May-18 2:29am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2018-05-29T06:35:00

Solution 1

If I understand your question, correctly, see: SQL BETWEEN Operator[^]

If you need this to continue change with each day, also look at: SQL Server DATEADD() Function[^] and thus automatically select a rolling date range based upon the current date from getdate()

Posted 29-May-18 6:35am

W∴ Balboos, GHB

CHill60 · Answer 2 · 2018-05-30T02:31:00

Instead of using numbers in your sequence cte and then doing a sub-query, just use dates. You can then have the start and end dates as variables. E.g. like this

SQL

declare @startdate date = '2018-FEB-01'
declare @enddate date = '2018-NOV-10'

;WITH cte AS
(
    SELECT  @startdate AS num
    UNION ALL
    SELECT  dateadd(mm, 1, num)
    FROM    cte
    WHERE dateadd(mm, 1, num) < @enddate
)
SELECT  month(num) as MonthID, 
        datename(month, num) as MonthName, 
        year(num) as [Year], 
        datename(m,num)+' '+cast(datepart(yyyy,num) as varchar) as MonthYearName
FROM    cte 
order by month(num) desc