CODEWITHSUNDEEP
sqlsql-servert-sql
Murali Murugesan
Murali Murugesan

Reputation: 22619

Select Past 24(N) Months First Date

Here is a script to display past 24 months first date.

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

The above query returns 24 result set(select). But I want in a single result set

Edit:

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

Upvotes: 2

Views: 983

Answers (6)

praveen
praveen

Reputation: 12271

You can use a recurcive 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:

If you want you can store it in table variable or temp table

 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

Or you can create a function 

 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

Now you can your function like 

 Select * from dbo.FnGetDate(24)

Upvotes: 3

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

DECLARE @intCount AS INT

SET @intCount = 24

DECLARE @Date AS VARCHAR(25)  

WHILE (@intCount > 0)
BEGIN
    SET @Date = ISNULL(@Date, '') + CONVERT(VARCHAR(25),DATEADD(m,-(@intCount-1),
                                DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) + ' '
    --SELECT @Date   Don't have it here
    SET @intCount = @intCount -1
END

SELECT @Date

Upvotes: 0

cha
cha

Reputation: 10411

here is an example how to do it with CTE:

;WITH DateCTE AS
    (
        SELECT dateadd(dd, - datepart(day, getdate()) + 1, Convert(date, getdate())) AS DateValue
        UNION ALL
        SELECT DATEADD(month, -1, DateValue)
        FROM DateCTE
        WHERE DATEADD(month, 23, DateValue) >  GetDate()
    )
select DateValue from DateCTE;

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32449

If @intCount is constant you can do that by simple UNION ALL:

select CONVERT(VARCHAR(25),DATEADD(m,-24, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) as date
union all
select CONVERT(VARCHAR(25),DATEADD(m,-23, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) as date
union all
.....
select CONVERT(VARCHAR(25),DATEADD(m,0, DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101) as date

Or use temp table:

Declare @intCount as int

SET @intCount = 24

Declare @Date as varchar(25)  

CREATE TABLE #temptable 
    (datefield date)

While (@intCount >0)

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

SET @intCount = @intCount-1

End
select * from #temptable
drop table #temptable

Upvotes: 1

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

Use temp table :

Declare @intCount as int
SET @intCount = 24    
Declare @Date as varchar(25)  
create table #temp1 (myDate date)
While (@intCount >0)
Begin
SET @Date = CONVERT(VARCHAR(25),DATEADD(m,-(@intCount-1),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())),101)
insert into #temp1
select @Date
SET @intCount = @intCount-1
End

select * from #temp1

Upvotes: 1

Scotch
Scotch

Reputation: 3226

This is kind of what I was suggesting, I'm rusty on stored procedures but I bolded my suggestions to your original procedure

Declare @intCount as int
  CREATE TABLE days (day varchar(25));
 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)
 INSERT Into days(day) VALUES (@Date)
 SET @intCount = @intCount-1

 End
 SELECT * FROM days;

Upvotes: 1

Related Questions