# CONNECT BY result

• iniyavan

SSC Enthusiast

Points: 127

Hi friends,

How can we get multiple dates and days for the given number? In this example, 10 is given with '01-Jul-2020'. So 10 dates and days from 01-Jul-2020 are displayed. Could you please help on this?

• Jeffrey Williams

SSC Guru

Points: 88651

This is a SQL Server forum and you have posted Oracle code...are you looking to convert that Oracle code to T-SQL?  If so, then something like this:

Declare @interval int = 10
, @fromDate datetime = '2020-07-01';

With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select Top (@interval)
checksum(row_number() over(Order By @@spid))
From t t1, t t2
)
Select S_No = it.n
, dt.Dates
, Days = datename(weekday, dt.Dates)
From iTally it
Cross Apply (Values (dateadd(day, it.n - 1, @fromDate))) dt(Dates);

Or - you can do this:

Declare @interval int = 10
, @fromDate datetime = '2020-07-01';

With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, dateRange (Dates)
As (
Select Top (@interval)
dateadd(day, checksum(row_number() over(Order By @@spid)) - 1, @fromDate)
From t t1, t t2
)
Select S_No = datediff(day, @fromDate, dr.Dates) + 1
, dr.Dates
, Days = datename(weekday, dr.Dates)
From dateRange dr;

But - based on your other posts I think you really need a calendar table/function.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

• iniyavan

SSC Enthusiast

Points: 127

Thanks for the suggestions, Jeffery.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply