Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Marusyk
Advocate II
Advocate II

How to generate Calendar table in warehouse

I need to generate a Calendar table - it is a very simple task for regular SQL but I'm using Microsoft Fabric and I've tried so far many options, however no success due to Fabric SQL limitations.

 

My table is:

 

 

CREATE TABLE dbo.CalendarData
(
    DateValue DATE,
    Year INT,
    StartOfYear VARCHAR(20),
    EndOfYear VARCHAR(20),
    Month INT,
    StartOfMonth VARCHAR(20),
    EndOfMonth VARCHAR(20),
    DaysInMonth INT,
    Day INT,
    DayName VARCHAR(20),
    DayOfWeek INT,
    MonthName VARCHAR(20),
    Quarter INT,
    StartOfQuarter VARCHAR(20),
    EndOfQuarter VARCHAR(20)
);

 

 

Option 1: while loop says - Each SQL statement runs as an independent session. Session context does not persist across SQL statements. Learn more at https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-query-editor#limitati

 

 

DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
  INSERT INTO dbo.CalendarData (
    Date, Year, StartOfYear, EndOfYear,
    Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
    Day, DayName, DayOfWeek,
    Quarter, StartOfQuarter, EndOfQuarter
  )
  VALUES (
    @StartDate, 
    YEAR(@StartDate), -- Year
    DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
    DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
    MONTH(@StartDate), -- Month
    DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
    EOMONTH(@StartDate), -- EndOfMonth
    DAY(EOMONTH(@StartDate)), -- DaysInMonth
    DATENAME(MONTH, @StartDate), - MonthName
    DAY(@StartDate), -- Day
    DATENAME(WEEKDAY, @StartDate), -- DayName
    (DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
    DATEPART(QUARTER, @StartDate), -- Quarter
    CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
    EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
  );

  SET @StartDate = DATEADD(day, 1, @StartDate);
END;

 

 

Option 2:  CTE - The query references an object that is not supported in distributed processing mode

 

 

WITH Numbers AS (
    SELECT TOP (DATEDIFF(day, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.all_columns
  )
  INSERT INTO dbo.Calendar (
    Date, Year, StartOfYear, EndOfYear,
    Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
    Day, DayName, DayOfWeek,
    Quarter, StartOfQuarter, EndOfQuarter
  )
  SELECT 
    DATEADD(day, n - 1, @startDate) AS Date
    , YEAR(DATEADD(day, n - 1, @startDate)) AS Year
    , DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), 1, 1) AS StartOfYear
    , DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), 12, 31) AS EndOfYear
    , MONTH(DATEADD(day, n - 1, @startDate)) AS Month
    , DATENAME(MONTH, DATEADD(day, n - 1, @startDate)) AS MonthName
    , DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), MONTH(DATEADD(day, n - 1, @startDate)), 1) AS StartOfMonth
    , EOMONTH(DATEADD(day, n - 1, @startDate)) AS EndOfMonth
    , DAY(EOMONTH(DATEADD(day, n - 1, @startDate))) AS DaysInMonth
    , DAY(DATEADD(day, n - 1, @startDate)) AS Day
    , DATENAME(WEEKDAY, DATEADD(day, n - 1, @startDate)) AS DayName
    , (DATEPART(weekday, DATEADD(day, n - 1, @startDate)) + 5) % 7 + 1 AS DayOfWeek
    , DATEPART(QUARTER, DATEADD(day, n - 1, @startDate)) AS Quarter
    , CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DATEADD(day, n - 1, @startDate)), 0) AS DATE) AS StartOfQuarter
    , EOMONTH(DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), DATEPART(QUARTER, DATEADD(day, n - 1, @startDate)) * 3, 1)) EndOfQuarter
   FROM Numbers

 

 

Option 3: Insert from Table function - The query references an object that is not supported in distributed processing mode.

 

 

INSERT INTO dbo.Calendar (
    Date, Year, StartOfYear, EndOfYear,
    Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
    Day, DayName, DayOfWeek,
    Quarter, StartOfQuarter, EndOfQuarter
  )
  SELECT 
    dr.Date AS Date
    , YEAR(dr.Date) AS Year
    , DATEFROMPARTS(YEAR(dr.Date), 1, 1) AS StartOfYear
    , DATEFROMPARTS(YEAR(dr.Date), 12, 31) AS EndOfYear
    , MONTH(dr.Date) AS Month
    , DATENAME(MONTH, dr.Date) AS MonthName
    , DATEFROMPARTS(YEAR(dr.Date), MONTH(dr.Date), 1) AS StartOfMonth
    , EOMONTH(dr.Date) AS EndOfMonth
    , DAY(EOMONTH(dr.Date)) AS DaysInMonth
    , DAY(dr.Date) AS Day
    , DATENAME(WEEKDAY, dr.Date) AS DayName
    , (DATEPART(weekday, dr.Date) + 5) % 7 + 1 AS DayOfWeek
    , DATEPART(QUARTER, dr.Date) AS Quarter
    , CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, dr.Date), 0) AS DATE) AS StartOfQuarter
    , EOMONTH(DATEFROMPARTS(YEAR(dr.Date), DATEPART(QUARTER, dr.Date) * 3, 1)) EndOfQuarter
   FROM dbo.GenerateDateRange(@startDate, @endDate) AS dr;

 

 

 How to generate simple data for table in Fabric??

1 ACCEPTED SOLUTION

Hi @Marusyk 
The first one works, it just had some syntax issues (Date instead of DateValue for the first field, a comment that a '-' instead of '--' and MonthName was in the wrong spot in the list of fields to insert into. Here is the updated script:

DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
  INSERT INTO dbo.CalendarData (
    DateValue, Year, StartOfYear, EndOfYear,
    Month, StartOfMonth, EndOfMonth, DaysInMonth, MonthName,
    Day, DayName, DayOfWeek,
    Quarter, StartOfQuarter, EndOfQuarter
  )
  VALUES (
    @StartDate,
    YEAR(@StartDate), -- Year
    DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
    DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
    MONTH(@StartDate), -- Month
    DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
    EOMONTH(@StartDate), -- EndOfMonth
    DAY(EOMONTH(@StartDate)), -- DaysInMonth
    DATENAME(MONTH, @StartDate), -- MonthName
    DAY(@StartDate), -- Day
    DATENAME(WEEKDAY, @StartDate), -- DayName
    (DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
    DATEPART(QUARTER, @StartDate), -- Quarter
    CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
    EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
  );
 
  SET @StartDate = DATEADD(day, 1, @StartDate);
END;


I have created a repro for you:


vnikhilanmsft_0-1713895493017.png

Hope this helps. Please let me know if you have any further queries.

 

View solution in original post

5 REPLIES 5
AlexanderPowBI
Resolver I
Resolver I

Hi,

I have used this as a base:
https://fabric.guru/comprehensive-date-dimension-table-for-power-bi-datasets-in-fabric
and made adjustments to fit my needs. Then I have created a view in my warehouse based on that table and further requirements. Maybe it can help you?

//Alexander

Thank you, it is very useful but I don't use Spark

Hi @Marusyk 
The first one works, it just had some syntax issues (Date instead of DateValue for the first field, a comment that a '-' instead of '--' and MonthName was in the wrong spot in the list of fields to insert into. Here is the updated script:

DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
  INSERT INTO dbo.CalendarData (
    DateValue, Year, StartOfYear, EndOfYear,
    Month, StartOfMonth, EndOfMonth, DaysInMonth, MonthName,
    Day, DayName, DayOfWeek,
    Quarter, StartOfQuarter, EndOfQuarter
  )
  VALUES (
    @StartDate,
    YEAR(@StartDate), -- Year
    DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
    DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
    MONTH(@StartDate), -- Month
    DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
    EOMONTH(@StartDate), -- EndOfMonth
    DAY(EOMONTH(@StartDate)), -- DaysInMonth
    DATENAME(MONTH, @StartDate), -- MonthName
    DAY(@StartDate), -- Day
    DATENAME(WEEKDAY, @StartDate), -- DayName
    (DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
    DATEPART(QUARTER, @StartDate), -- Quarter
    CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
    EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
  );
 
  SET @StartDate = DATEADD(day, 1, @StartDate);
END;


I have created a repro for you:


vnikhilanmsft_0-1713895493017.png

Hope this helps. Please let me know if you have any further queries.

 

Hi @Marusyk 
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

v-nikhilan-msft
Community Support
Community Support

Hi @Marusyk 

Thanks for using Fabric Community. 
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.