sql - 选择过去 24(N) 个月的第一个日期

Question

这是一个脚本，用于显示过去 24 个月的第一个日期。

I need the following functionality in a single T-SQL query instead of the iteration.

Declare @intCount as int

SET @intCount = 24

Declare @Date as varchar(25)  

While (@intCount >0)

Begin
SET @Date = CONVERT(VARCHAR(25),DATEADD(m,-(@intCount-1),
                                DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101)
select @Date

SET @intCount = @intCount-1

End

以上查询返回 24 个结果集(选择)。但我想要一个结果集

编辑:

The main requirement is to use this single result with in a sub query

Answer 1

您可以使用递归 CTE

;with cte(intCount,myDate)
 as
 (
   Select 1, CONVERT(VARCHAR(25),DATEADD(m,  1,
                            DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101)
   union all
    Select intCount+1 ,CONVERT(VARCHAR(25),DATEADD(m,-(intCount-1),
                            DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) from cte
                            where intCount<=24
 )
Select myDate from  cte

Updated:

如果需要，可以将其存储在表变量或临时表中

 Declare @Date table
 (myDate varchar(25))

 Declare @count int
 set @count=24
 ;with cte(intCount,myDate)
 as
 (
   Select @count-1, CONVERT(VARCHAR(25),DATEADD(m,-(@count-1),
                            DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101)
   union all
   Select intCount-1 ,CONVERT(VARCHAR(25),DATEADD(m,-(intCount-1),
                            DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) from cte
                            where intCount>0
 )
 Insert into @Date(myDate) 
 Select myDate from cte

或者你可以创建一个函数

 go
 alter FUNCTION FnGetDate(@intCount int)
 RETURNS  @rtnTable TABLE 
 (
  myDate varchar(25)NOT NULL
 )
 AS
 BEGIN

 ;with cte(level,myDate)
  as
 (
   Select @intCount-1, CONVERT(VARCHAR(25),DATEADD(m,-(@intCount-1),
                        DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101)
   union all
   Select level-1 ,CONVERT(VARCHAR(25),DATEADD(m,-(level-1),
                        DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) from cte
                        where level>0
 ) 
 Insert into @rtnTable(myDate)
 select myDate from cte
 return
 END

现在你可以像

 Select * from dbo.FnGetDate(24)