1
nDays := Round( dEndTime - dStartTime ) + 1; 

For i in 1..7 Loop
 nDay := i + 1;
    if i = 7 Then
      nDay := 1;
    End If;

SELECT To_Date(To_Char((dStartTime+Level-1),'DD.MM.YYYY')||' 00:00','DD.MM.YYYY HH24:MI'),
To_Date(To_Char((dStartTime+Level-1),'DD.MM.YYYY')||' 23:59','DD.MM.YYYY HH24:MI')
FROM DUAL
WHERE To_Char( dStartTime + Level -1 , 'd' ) =  To_Char(nDay)
CONNECT BY Level <= nDays;

End Loop;

output:

22-JUL-12
23-JUL-12
18-JUL-12
19-JUL-12
20-JUL-12
21-JUL-12

I need to convert this query to SQL Server 2008, please help to find workaround with the same......

I have tried above output with single query with nDay from 1 to 7.....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chirag Mehta
  • 9
  • 1
  • 2
  • Take a look at [this SO question](http://stackoverflow.com/questions/2200636/oracle-connect-by-clause-equivalent-in-sql-server), you can use CTE's in SQL server similarly to Oracle's connect by (and recursive CTE's are now the ANSI standard way of moving through a hierarchy). – N West Jul 17 '12 at 13:11

1 Answers1

1

You can do this with a recursive CTE. However, that syntax can be hard to remember, so when I need a handful of items, I do something like:

select *
from (select row_number() over (order by (select NULL)) as seqnum
      from information_schema.columns
     ) t
where seqnum < <value>

Any table can be used. I just put in the INFORMATION_SCHEMA.columns table, because it is easy and generally has dozens or hundreds of rows.

Your output doesn't match the query. The following generates all dates between two values that are reasonably close together, Here is an example:

declare @dstarttime date = '2012-07-11', @dendtime date=  '2012-07-13';
with const as (select @dstarttime as dStartTime, @dendtime as dendTime)
SELECT DATEADD(d, seqnum - 1, dStartTime)
FROM (select *
      from (select row_number() over (order by (select NULL)) as seqnum, const.*
            from information_schema.columns cross join const
           ) t
      where seqnum <= DATEDIFF(d, dStartTime, dendTime) + 1
     ) t

As I said, you can also do this with recursive CTEs or, if you have one, with a calendar table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thnks for your rpl Gordon Linoff, but as per your given solution i already used it, by below also give same output as yours...., but i want as per my given output... WITH DateRange(dt) AS ( SELECT CONVERT(datetime, '2009-01-01') dt UNION ALL SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, '2009-01-10') ) SELECT dt FROM DateRange – Chirag Mehta Jul 18 '12 at 10:01