CODEWITHSUNDEEP
sql-serveroracle-database
gpa
gpa

Reputation: 2451

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

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);

Upvotes: 4

Views: 3047

Answers (2)

gpa
gpa

Reputation: 2451

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;

Upvotes: 3

podiluska
podiluska

Reputation: 51504

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

Upvotes: 5

Related Questions