如何在SQL中的特定日期之间选择最近5个月的名称 [英] How to select last 5 month name between specific date in SQL

问题描述

您好我想从当前日期获取最近5个月名称的列表。和使用下面的SQL查询我得到它但在这里我想获得特定日期之间的列表如何获取特定日期之间的最后5个月的列表。



我尝试过：

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

推荐答案

如果我理解你的问题，请参阅： SQL BETWEEN运营商 [ ^ ]



如果您需要每天继续更改，请查看： SQL Server DATEADD（）函数 [ ^ ]因此从getdate（）

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()

中自动选择基于当前日期的滚动日期范围而不是在序列中使用数字cte然后做一个子查询，只需使用日期。然后，您可以将开始日期和结束日期作为变量。例如。像这样

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

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

这篇关于如何在SQL中的特定日期之间选择最近5个月的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！