示例查询以查找sql​​中两个月之间的月份列表 [英] sample query to find list of months in between two months in sql

问题描述

嗨


我使用四个dropdowmlist，

来自范围我使用一个下拉（月）和一个下拉（年）

为范围我使用一个下拉（月）和一个下拉（年）

当我选择

从范围到范围

例如

jan2013

may2013

表示



输出应

jan2013

feb2013

mar2013

apr2013

may2013



有可能吗？

任何建议请................！

Hi
I used four dropdowmlist,
for from range i use one dropdown(month) and one dropdown(year)
for to range i use one dropdown(month) and one dropdown(year)
when i select
from range and to range
for example
jan2013
may2013
means

output should be
jan2013
feb2013
mar2013
apr2013
may2013

is it possible?
any suggestion pls................!

推荐答案

看看例子：

Have a look at example:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME

SET @startdate = '2013-01-01'
SET @enddate = '2013-05-01'

;WITH Months AS
(
	SELECT @startdate AS MyDate
	UNION ALL
	SELECT DATEADD(mm,1,MyDate) AS MyDate
	FROM Months
	WHERE DATEADD(mm,1,MyDate) <= @enddate
)
SELECT CONVERT(VARCHAR(30), MyDate, 121) AS MyMonths, LEFT(DATENAME(MONTH ,MyDate),3) + CONVERT(VARCHAR(4),YEAR(MyDate)) AS MyMonths1
FROM Months

结果：

Result:

MyMonths                MyMonths1
2013-01-01 00:00:00.000 Jan2013
2013-02-01 00:00:00.000 Feb2013
2013-03-01 00:00:00.000 Mar2013
2013-04-01 00:00:00.000 Apr2013
2013-05-01 00:00:00.000 May2013

以上查询使用 CTE [ ^ ]，但可以使用 DO ... WHILE [ ^ ]循环。

Above query uses CTE[^], but it is possible to achieve that using DO...WHILE[^] loop.

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20120101'        
        ,@EndDate   = '20130805';


SELECT  convert(varchar,DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate))) +convert(varchar,DATENAME(YEAR , DATEADD(MONTH , x.number, @StartDate))) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

参考： [ ]

我希望这对您有用。

Reference: []
I hope this will be useful for you.

这篇关于示例查询以查找sql​​中两个月之间的月份列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！