两个日期之间的月份和年份列表 [英] List of Months and years between two dates

问题描述

大家好，



我有一种情况需要显示保存在数据库中的两个日期之间的年份列表，即BeginDate和Enddate。我研究并发现了一个SQL函数，显示两个日期之间的月份，但我不知道同时获得数月/年。

Ex：开始日期（MM / YY / YEAR）：03/02 / 2012 EndDate：10/01/2012



我应该在2012年3月，2012年4月，2012年5月...... 2012年10月获得输出。或者它可以是03 / 2012,04 / 2012,05 / 2012 ... 10/2012。最后，我想动态地在下拉列表中显示此输出。



这是我修改过的功能，只显示了几个月，即三月，四月，五月......十月以上的日期。如果你能帮我这方面的话，我将不胜感激。

  ALTER   PROCEDURE  dbo.Addmonthslist 
 
  @ BeginDate   AS  日期时间，
  @ EndDate   AS  日期时间，
  @ ProjectID   AS   varchar （ 50 ）
  AS  
  SELECT  
 BeginDate，
 EndDate 
 来自 tblProjects 其中 ProjectID =  @ ProjectID  
 
 
  SELECT  DATENAME（MONTH，DATEADD（MONTH，x） .nu​​mber， @ BeginDate ）） AS  MonthName 
  FROM  master.dbo.spt_values x 
  WHERE  x。 type  = < span class =code-string>'  P' 
  AND  x .nu​​mber< = DATEDIFF（MONTH， @ BeginDate ， @ EndDate ）; 

解决方案

  ALTER  程序 dbo.Addmonthslist 
 
  @ BeginDate   AS  日期时间，
  @ EndDate   AS  < span class =code-keyword>日期时间，
  @ ProjectID   AS   varchar （ 50 ）
  AS  
  SELECT  
 
 BeginDate， 
 EndDate 
 来自 tblProjects 其中 ProjectID =  @ProjectID  
 
 
  SELECT   DATENAME（MONTH，DATEADD（MONTH，x.number，  @ BeginDate ））+ '   - ' + 转换（ varchar （ 4 ），年（DATEADD（MONTH，x.number， @ BeginDate ）））  AS  MonthName 
  FROM  master.dbo.spt_values x 
  W HERE  x。 type  = '  P'  
  AND  x.number< = DATEDIFF（MONTH， @ BeginDate ，  @ EndDate ）; 
 
 返回 

快乐编码！

： ）

Hello all,

I have a situation in which I need to display list of months with years between two dates saved in database i.e BeginDate and Enddate. I researched and found a SQL function that displays months between two dates but I have no idea about obtaining months/year at same time.
Ex: Begin Date(MM/YY/YEAR): 03/02/2012 EndDate : 10/01/2012

I should get output as March/2012,April/2012,May/2012 ....October/2012. or it can be 03/2012,04/2012,05/2012...10/2012. Ultimately I would like to display this output in a dropdown dynamically.

Here is the function I have modified that displays only months i.e March,April,May...October for above dates. I would appreciate if you could help me with this regard.

	ALTER PROCEDURE dbo.Addmonthslist
	
	@BeginDate AS Datetime,
	@EndDate AS Datetime,
	@ProjectID AS varchar(50)
AS
SELECT
 BeginDate,
 EndDate
 from tblProjects where ProjectID = @ProjectID
 

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

解决方案

ALTER PROCEDURE dbo.Addmonthslist
	
	@BeginDate AS Datetime,
	@EndDate AS Datetime,
	@ProjectID AS varchar(50)
AS
SELECT
 
 BeginDate,
 EndDate
 from tblProjects where ProjectID = @ProjectID
 
 
SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @BeginDate)) + '-' + Convert(varchar(4), Year(DATEADD(MONTH, x.number, @BeginDate))) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'
AND     x.number <= DATEDIFF(MONTH, @BeginDate, @EndDate);
	
	RETURN

Happy Coding!
:)

这篇关于两个日期之间的月份和年份列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！