| |

How To Set The First Day of Week DATEFIRST

ByMaki Pa

How To Set The First Day of Week DATEFIRST – SET DATEFIRST = 1 sets the first day of the week to Monday. In SQL Server, the first day of the week can be set to any day from 1 (Monday) to 7 (Sunday). This setting affects the output of date-related functions like DATEPART and DATENAME. For example, if DATEFIRST is set to 1 (Monday), DATEPART(dw, ‘2024-12-29’) will return 7, because December 29, 2024, is a Sunday. SET DATEFIRST 1.

When to Use SET DATEFIRST

Regional Settings: Use SET DATEFIRST to match the first day of the week with regional or cultural preferences. For example, in many European countries, Monday is considered the first day of the week.

  • Consistent Reporting: Ensure consistency in date-related calculations and reports, especially when working with international data or users from different regions.
  • Custom Calculations: When you need specific calculations that depend on the first day of the week, such as weekly summaries or reports.

How To Set The First Day of Week DATEFIRST

Here is the code which generate 

-- Declare and initialize variables
DECLARE @i INT = 0;                  -- Loop counter
DECLARE @dt DATE;                   -- Current date in loop
DECLARE @dt_blost DATE;            -- Adjusted date based on weekday
DECLARE @tt TABLE (                -- Table variable to store results
    id INT IDENTITY,              -- Auto-incrementing ID
    dt DATE,                      -- Original date
    wd INT,                       -- Weekday number
    dt_blost DATE                 -- Adjusted date
);
DECLARE @wd INT;                   -- Weekday number

-- Set the first day of the week to Monday (1 = Monday, 7 = Sunday)
SET DATEFIRST 1;

-- Initialize @dt with the current date
SET @dt = GETDATE();

-- Loop 20 times to generate dates and adjusted dates
WHILE @i < 20
BEGIN
    -- Add @i days to the current date
    SET @dt = DATEADD(DAY, @i, @dt);

    -- Get the weekday number of the new date
    SET @wd = DATEPART(WEEKDAY, @dt);

    -- Calculate adjusted date (@dt_blost) based on weekday:
    -- If Monday (1), subtract 3 days
    -- If Tuesday (2), subtract 1 day
    -- Otherwise, add 2 days
    SET @dt_blost = DATEADD(DAY,
        IIF(@wd = 1, -3,
            IIF(@wd = 2, -1, 2)), @dt);

    -- Insert the date, weekday, and adjusted date into the table
    INSERT @tt
    SELECT @dt AS dt, @wd AS wd, @dt_blost;

    -- Increment loop counter
    SET @i += 1;
END

-- Display the results
SELECT * FROM @tt;

-- Additional check: show current UTC date, DATEFIRST setting,
-- weekday number and name for the current UTC date
SET DATEFIRST 1;
SELECT 
    GETUTCDATE() AS [getutcdate],
    @@DATEFIRST AS [DATEFIRST],
    DATEPART(WEEKDAY, GETUTCDATE()) AS [datepart_WEEKDAY],
    DATENAME(WEEKDAY, GETUTCDATE()) AS [datename_WEEKDAY];

Here’s a code in action

How To Set The First Day of Week DATEFIRST in sql
How To Set The First Day of Week DATEFIRST

Explanation

  1. Variable Declarations: The code declares and initializes several variables to store dates, weekday numbers, and a temporary table to hold the results.
  2. SET DATEFIRST: This sets the first day of the week to Monday, which affects the output of date-related functions.
  3. Loop: The loop runs 20 times, incrementing the date by the counter value each time.
  4. Date Adjustments: Based on the weekday, the code adjusts the date:
    • If the date is Sunday (weekday 1), it subtracts 3 days.
    • If the date is Monday (weekday 2), it subtracts 1 day.
    • For other weekdays, it adds 2 days.
  5. Insert into Temporary Table: The current date, weekday number, and adjusted date are inserted into the temporary table.
  6. Select from Temporary Table: The code selects all records from the temporary table to display the results.
  7. Final Select: The code selects the current UTC date, the DATEFIRST setting, the weekday number, and the weekday name to show the current settings and date information.

This script is useful for generating a series of dates, adjusting them based on specific rules, and storing the results for further analysis. If you have any more questions or need further clarification, feel free to ask!

You can read about date and time most important functions in post Python for analysts most important datetime functions

SQL Date and Time Functions for Power BI Date Tables

When building reports in Power BI, a comprehensive Date Table is essential for enabling time-based calculations like YTD, MTD, and custom period comparisons. While Power BI can auto-generate a date table, using SQL to create one gives you full control over its structure and logic.

Let’s explore key SQL Server date and time functions and how to use them to build a robust date table.

Generating a Date Range

To create a date table, you need a continuous range of dates. In SQL Server, you can use a loop or a recursive CTE:

DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2030-12-31';

WITH DateCTE AS (
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateCTE
    WHERE DateValue < @EndDate
)
SELECT * INTO DateTable FROM DateCTE
OPTION (MAXRECURSION 32767);

This creates a table with one row per day between 2020 and 2030.

Alternate code you can find here How Works Recursive CTE in SQL Server?

Adding Date Attributes

Once you have the base dates, enrich them with useful columns:

ALTER TABLE DateTable ADD 
    Year INT,
    Month INT,
    MonthName VARCHAR(20),
    Quarter INT,
    Weekday INT,
    WeekdayName VARCHAR(20);

UPDATE DateTable
SET 
    Year = YEAR(DateValue),
    Month = MONTH(DateValue),
    MonthName = DATENAME(MONTH, DateValue),
    Quarter = DATEPART(QUARTER, DateValue),
    Weekday = DATEPART(WEEKDAY, DateValue),
    WeekdayName = DATENAME(WEEKDAY, DateValue);

These columns allow for flexible filtering and grouping in Power BI.

Fiscal Calendar and Flags

You can also add fiscal logic and flags:

ALTER TABLE DateTable ADD FiscalYear INT;

UPDATE DateTable
SET FiscalYear = CASE 
    WHEN MONTH(DateValue) >= 7 THEN YEAR(DateValue) + 1
    ELSE YEAR(DateValue)
END;

Add flags like IsWeekend, IsToday, or IsCurrentMonth to simplify DAX expressions.

Conclusion

SQL’s date and time functions like DATEADD, DATEPART, DATENAME, and YEAR are powerful tools for building a custom date table. Once created, export it to Power BI or use it as a view for dynamic reporting.

Would you like a ready-to-run SQL script for a complete date table?

Post written with the help of https://copilot.microsoft.com/

Similar Posts