t-sql select 获取年份范围内的所有月份 [英] t-sql select get all Months within a range of years

问题描述

我需要一个选择以返回指定日期范围内的月份和年份，我将在其中输入开始年份和月份，选择将返回从我输入的日期到今天的月份和年份.

I need a select to return Month and year Within a specified date range where I would input the start year and month and the select would return month and year from the date I input till today.

我知道我可以在循环中执行此操作，但我想知道是否可以在一系列选择中执行此操作?

I know I can do this in a loop but I was wondering if it is possible to do this in a series selects?

Year  Month
----  -----
2010  1
2010  2
2010  3
2010  4
2010  5
2010  6
2010  7

等等.

推荐答案

declare @date1 datetime, 
    @date2 datetime, 
    @date  datetime, 
    @month integer, 
    @nm_bulan varchar(20) 

create table #month_tmp 
    ( bulan integer null, keterangan varchar(20) null ) 

select @date1 = '2000-01-01', 
       @date2 = '2000-12-31' 

select @month = month(@date1) 

while (@month < 13) 
Begin 
    IF @month = 1 
    Begin 
       SELECT @date  = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,0,@date1))-1),DATEADD(mm,0,@date1)),111) + ' 00:00:00' as DATETIME ) 
    End
    ELSE
    Begin
       SELECT @date  = CAST( CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,@month -1,@date1))-1),DATEADD(mm,@month -1,@date1)),111) + ' 00:00:00' as DATETIME ) 
    End
    select @nm_bulan = DATENAME(MM, @date)

    insert into #month_tmp
    select @month as nilai, @nm_bulan as nama 

    select @month = @month + 1
End 

select * from #month_tmp 
drop table #month_tmp 
go

这篇关于t-sql select 获取年份范围内的所有月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！