CONNECT BY result
-
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?
-
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
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