How to Create Calendar Table in SQL Server

We'll get to know the techniques:

1. CREATE TABLE, CALCULATED COLUMN:

• You can create a table with calculated columns that derive their value from expressions.

  CREATE TABLE ExampleCalculation (
    ID INT PRIMARY KEY,
    Value1 INT,
    Value2 INT,
    Sum AS (Value1 + Value2)
  );
  

2. Get part of the date (

• To get specific parts of a date, you can use the DATEPART, DATENAME, YEAR, MONTH, and DAY functions.

  Select 
    DATEPART(YEAR, GETDATE()) AS Year,
    DATENAME(MONTH, GETDATE()) AS SameName,
    MONTH(GETDATE()) AS SameNumber,
    DAY(GETDATE()) AS Day;
  

3. Combine Column Values (

• The CONCAT function combines column values into a single string.

  SELECT CONCAT(FirstName, ' ', LastName) AS FullName
  FROM PeopleTable;
  

4. Get minimum and maximum date of selected tables (MIN, MAX):

• Use the MIN and MAX functions to get the minimum and maximum values of a column.

  SELECT MIN(Date) AS

  Minimum Date, MAX(Date) AS MaximumDate
  FROM DateTable;
  

5. Combine All Table Records (

• Combine results from two queries using UNION ALL.

  SELECT Column FROM Table1
  UNION ALL
  SELECT Column FROM Table2;
  

6. Date hierarchy with Common Table Expression (

• Use a CTE to create a hierarchy of dates.

  WITH AS DateHierarchy (
    SELECT 
        Date
        YEAR(Date) AS YEAR,
        MONTH(Date) AS Month
    FROM DateTable
  )
  SELECT * FROM HierarchyDate;
  

7. Insert Records into Calendar Table (INSERT):

• Insert records into a calendar table.

  INSERT INTO Calendar (Date, DayWeek, Month, Year)
  VALUES 
    ('2023-01-01', 'Sunday', 'January', 2023),
    ('2023-01-02', 'Monday', 'January', 2023),
    ...;
  

These examples are illustrative and may need to be tailored to meet the specific needs of your database environment and schema.