-2

I am trying to understand what does this line of Oracle script is doing in order for me to translate into SQL Server.

table(cast(multiset(select trunc(sysdate)-level 
                   from dual 
                   connect by level <= 100) as sys.odcidatelist))

Can someone help me translate this script into SQL Server, and also explain what is this script doing?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marina Kamal
  • 7
  • 2

2 Answers2

1

If you don't have a calendar table (highly recommended), you can use an ad-hoc tally table.

Example

Select Top 100 D=convert(date,dateadd(DAY,-row_number() Over (Order By (Select NULL))+1,getdate())) 
 From master..spt_values  -- Any Table of appropiate size would do.
 Order By D desc

Results

D
2021-12-06
2021-12-05
2021-12-04
2021-12-03
...
2021-09-01
2021-08-31
2021-08-30
2021-08-29
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

Most probably get a list of dates of the past 100 days from the current date.

You will get a list of two past dates you run the following SQL in Oracle. For e.g. if you run on 07-DEC-2021, then you will get two dates 06-DEC-2021 and 05-DEC-2021.

select * from
table(
    cast(
        multiset(
            select trunc(sysdate)-level from dual
            connect by level <= 2
        ) as sys.odcidatelist
    )
)

To get an equivalent function in SQL Server, you can refer to the below post.

Get a list of dates between two dates using a function

Mrigank Vallabh
  • 54
  • 1
  • 8
  • 1
    Yep. Which can then be joined to other tables with date interval data to count events by day, even when events lasts for several days. – Chris Maurer Dec 07 '21 at 04:40