How to create a data or calendar table within Dremio?

I’m looking to have a “Calendar” table created where it will give me the date between ranges ,say the past 5 years through today, or the past 5 years to the next 5 years. I’ve done something similar from data from a dataset but I need to ensure this table has dates that do not exist in the dataset I am querying from.

For example, here is what I’m doing today:

    DISTINCT Date_YearMonthDay, 
    CONCAT(TO_CHAR(Date_YearMonthDay, 'yyyy'), '-', 
    TO_CHAR(Date_YearMonthDay, 'MM')) as CalYearMonth, 
    TO_CHAR(Date_YearMonthDay, 'yyyy') as CalYear, 
    TO_CHAR(Date_YearMonthDay, 'MON') as CalMonth
        SELECT DISTINCT CreatedDate as Date_YearMonthDay FROM TableWithDates
ORDER BY DATE_YearMonthDay desc 

However, I’d like Dremio to provide me with the TableWithDates based on the range I’m looking for. Similar to what is posted here:

Sure I can do this the physical database/datasource, but I’d rather do it in Dremio for a variety of reasons.


In the example you have provided, SQL Server has table called Calendar which they run a loop to do this. Dremio does not have one. Is your ask to have a table like SQL server?


Well, I’d like to create this somehow, whether its a loop process, or something like in DAX where you can do CALENDARAUTO()

I am using this currently in Power BI and would like Dremio to perform this for me as a VDS so I can re-use in multiple places, rather than siloed in Power BI Model.

I know this thread is old, but I recently had the same problem and here’s what I came up with:

(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON'))),
(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON'))),
(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON')))

This query uses the FLATTEN function to create 16x16x16 = 4096 rows, numbers them from 0 to 4095 and then uses the DATE_SUB function to create the dates.
If you want a range between the past 5 years and the next 5 years, you could just change CURRENT_DATE to DATE_ADD(CURRENT_DATE, CAST(5 AS INTERVAL YEAR)) and add a WHERE-clause.
If you want to have even more days, you could just add more comma-separated zeros to the json string.