Convert Oracle sql with "CONNECT BY LEVEL" to T-SQL

Question

Can anyone help to converting following sql to T-SQL?

SELECT *
FROM (    SELECT LEVEL n, TO_DATE ('31/12/2010', 'DD/MM/YYYY') + NUMTODSINTERVAL (LEVEL, 'day') CurrDate
          FROM DUAL
    CONNECT BY LEVEL <= 2000);

podiluska · Accepted Answer · 2013-10-15 17:15:16Z

5

This should do the trick, I think

select dateadd(d, number, '2010-12-31') 
from master..spt_values 
where type='p' 
and number between 1 and 2000

answered Oct 15, 2013 at 17:15

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

podiluska Over a year ago

For our purposes, it's a table of numbers.

gpa · Accepted Answer · 2013-10-15 17:15:36Z

3

nevermind... found it...

with n as
(
SELECT TOP (DATEDIFF(DAY, '2010-12-31', '2015-12-31') + 1) 
n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects
)
 SELECT DATEADD(DAY, n-1, '2010-12-31')
 FROM n;

answered Oct 15, 2013 at 17:15

gpa

2,4517 gold badges42 silver badges71 bronze badges

Collectives™ on Stack Overflow

Convert Oracle sql with "CONNECT BY LEVEL" to T-SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related