Select Past 24(N) Months First Date

Question

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

What about inserting them into a dimension-esque table while you're iterating, and then just querying that table? — Scotch, Feb 21 '13 at 04:37
@Scotch, I am not getting you. You mean temp table? I am not good at SQL :( Could you please add your answer, so i can understand :) — Murali Murugesan, Feb 21 '13 at 04:38
what have you tried? Have a read about recursive CTE here: http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx — cha, Feb 21 '13 at 04:51
@cha, How this Recursive CTE will fit here. Sorry am not that expert in SQL :( — Murali Murugesan, Feb 21 '13 at 04:53
Hey @Murali, please could you go back and 'tick' which question is closest to what you want: http://stackoverflow.com/questions/14977496/calling-href-in-callback-function/ Thank you :) — Jimbo, Feb 21 '13 at 09:32

praveen · Answer 1 · 2013-02-21T05:34:45.687

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)

Why i am getting 03/01/2013, 03/01/2013 then 02/01/2013. Seems like first record is two times :) — Murali Murugesan, Feb 21 '13 at 04:55
@praveen: The answer still gives one extra date, including one month into the future. Fix that and it will probably answer the question. — gmm, Feb 21 '13 at 05:01
Just didn't check the condition in the question properly .Updated my ans — praveen, Feb 21 '13 at 05:08
Check my 2nd answer below `Update` .It doesn't return `03/01/2013` — praveen, Feb 21 '13 at 05:21
`Select a.col1,a.col2 ,b.myDate from TableA a inner join (Select myDate ,Id from @Date) temp on temp.ID=a.ID` .Something like this we can do .But it will be clear if u say what you want to do with this query instead of just saying that i need to write `Subquery` — praveen, Feb 21 '13 at 06:38

Andrey Gordeev · Answer 2 · 2013-02-21T04:51:30.737

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

This is again like an iteration. I will be sending N as a no of months. In that case i need to prepare as many select union all in loop :( — Murali Murugesan, Feb 21 '13 at 04:45

score 1 · Answer 3 · answered Feb 21 '13 at 04:47

1

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;

answered Feb 21 '13 at 04:47

Scotch

3,186
11
35
50

Looks nice. Is there a way i can do it with out iteration? – Murali Murugesan Feb 21 '13 at 04:51
Well, you're not iterating with your select in this example - so you're only getting one result set,I suppose Andrey's example isn't technically iterating as it's just unioning each conversion line by line. – Scotch Feb 21 '13 at 04:52

score 1 · Answer 4 · answered Feb 21 '13 at 04:50

1

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

answered Feb 21 '13 at 04:50

Grisha Weintraub

7,803
1
25
45

Is it possible to use with out iteration? – Murali Murugesan Feb 21 '13 at 04:51
I think praveen's approach is what you need. – Grisha Weintraub Feb 21 '13 at 04:59
Yeah Grisha. Thats what i am looking into now. Not sure about the query cost. Which will be better? :) – Murali Murugesan Feb 21 '13 at 05:00
I think the cost of `while` and `cte` will be pretty similar, especially when you have only 24 iterations... – Grisha Weintraub Feb 21 '13 at 05:12

score 1 · Answer 5 · answered Feb 21 '13 at 05:02

1

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;

answered Feb 21 '13 at 05:02

cha

10,301
1
18
26

This looks great. Can i use this inside sub query? – Murali Murugesan Feb 21 '13 at 05:08
sure, just add the table to the end of this query, something like (after `from dateCTE`): INNER JOIN Mytable on dateCTE.dateValue = Mytable.productiondate – cha Feb 21 '13 at 05:11

score 0 · Answer 6 · answered Feb 21 '13 at 05:32

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

Select Past 24(N) Months First Date

6 Answers6