Creating a calendar CTE ('or table) automaticaly

[fdecourt]

Hi everybody,
I am trying to use ALASQL to manipulate dates. I need it to be 100% in SQL (no javascript).
For that I need to create a CTE (so I can use it in my existing query) with all the dates from 2022-01-01 to 2080-12-31.
I tried many stuff like that :

WITH myRange AS (
  SELECT VALUE FROM RANGE(0, 21550)
),

calendar AS (
  SELECT DATEADD(day, VALUE, '2022-01-01') AS date
  FROM myRange
),

But in the calendar CTE, the VALUE is not usable.
If if do a
SELECT VALUE FROM myRANGE
I have the list of value from 0 to 21550
But I can't have use it in the DATEADD

Thanks a lot for your help

[Charlesnorris509]

Hey @fdecourt

The reason why you are experiencing these difficulties, is because AlaSql doesnt support all the traditional feature of Nosql database, Here possible solution specific to your use case:
convert each integer into a date by adding it to the base date (2022-01-01) using JavaScript's new Date() function within the SQL query.
Format the resulting date as a string in the desired format (YYYY-MM-DD).