Translate Oracle query to SQL Server-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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