How to Get Weekend Date in SQL Server

by

In this comprehensive article, I’ll share multiple methods to identify, filter, and work with weekend dates in SQL Server. These techniques have helped me solve complex reporting challenges for clients, ensuring accurate business intelligence and operational reporting.

How to Get Weekend Date in SQL Server

SQL Server Date Functions Overview

Before diving into weekend detection methods, it’s essential to understand SQL Server’s date handling capabilities. The key function for weekend detection is DATEPART() and DATENAME(), which allow us to extract day-of-week information from datetime values.

Day-of-Week Numbering in SQL Server

SQL Server represents days of the week numerically, but the exact values depend on your @@DATEFIRST setting:

DayDefault (Sunday=1)Monday First (@@DATEFIRST=1)
Sunday17
Monday21
Tuesday32
Wednesday43
Thursday54
Friday65
Saturday76

Understanding this variation is crucial because different organizations may have different DATEFIRST where their international operations required consistent weekend logic across different regional settings.

Method 1: Using the DATEPART Function for Weekend Detection

Basic Weekend Identification Query

The most straightforward method to identify weekend dates uses the DATEPART() function with the WEEKDAY parameter.

-- Method 1: Basic weekend detection using DATEPART
-- This query identifies weekend dates regardless of DATEFIRST setting

SELECT 
    Order_Date,
	Order_name,
    Order_ID,
    DATENAME(WEEKDAY, Order_Date) AS DayName,
    DATEPART(WEEKDAY, Order_Date) AS DayNumber,
    CASE 
        WHEN DATEPART(WEEKDAY, Order_Date) IN (1, 7) AND @@DATEFIRST = 7 THEN 'Weekend'
        WHEN DATEPART(WEEKDAY, Order_Date) IN (6, 7) AND @@DATEFIRST = 1 THEN 'Weekend'
        WHEN DATEPART(WEEKDAY, Order_Date) IN (1, 2) AND @@DATEFIRST = 6 THEN 'Weekend'
        ELSE 'Weekday'
    END AS DateType
FROM OrdersNew
WHERE Order_Date >= '2025-01-01'
    AND Order_Date < '2025-07-01'
ORDER BY Order_Date;

After executing the above query, I got the expected output as shown in the screenshot below.

How to Get Weekend Date in SQL Server

Universal Weekend Detection Function

To handle different DATEFIRST settings universally. Below is a reliable custom function for one of the requirements.

-- Universal weekend detection that works with any DATEFIRST setting
-- This approach uses DATENAME which is independent of DATEFIRST

SELECT 
    TransactionDate,
    CustomerID,
    Amount,
    DATENAME(WEEKDAY, TransactionDate) AS DayName,
    CASE 
        WHEN DATENAME(WEEKDAY, TransactionDate) IN ('Saturday', 'Sunday') 
        THEN 'Weekend'
        ELSE 'Weekday'
    END AS DateType,
    CASE 
        WHEN DATENAME(WEEKDAY, TransactionDate) IN ('Saturday', 'Sunday') 
        THEN 1 
        ELSE 0 
    END AS IsWeekend
FROM Transactions
WHERE TransactionDate >= DATEADD(MONTH, -1, GETDATE())
ORDER BY TransactionDate DESC;

After executing the above query, I got the expected output as shown in the screenshot below.

get weekend date in sql server

Method 2: Filtering Weekend Records from Query Results

Excluding Weekend Data

Many business scenarios require excluding weekend data from analysis. Here’s how I typically handle this requirement:

-- Method 2A: Exclude weekend records from business analysis
-- Useful for weekday-only business metrics

DECLARE @StartDate DATE = '2024-01-01'
DECLARE @EndDate DATE = '2024-12-31'

SELECT 
    ProductCategory,
    COUNT(*) AS WeekdayOrders,
    SUM(OrderAmount) AS WeekdayRevenue,
    AVG(OrderAmount) AS AvgWeekdayOrderValue,
    MIN(OrderDate) AS FirstWeekdayOrder,
    MAX(OrderDate) AS LastWeekdayOrder
FROM SalesData
WHERE OrderDate >= @StartDate 
    AND OrderDate <= @EndDate
    AND DATENAME(WEEKDAY, OrderDate) NOT IN ('Saturday', 'Sunday')
GROUP BY ProductCategory
HAVING COUNT(*) >= 10  -- Minimum threshold for statistical significance
ORDER BY WeekdayRevenue DESC;

Including Only Weekend Data

For weekend-specific analysis:

-- Method 2B: Include only weekend records
-- Perfect for weekend promotion analysis

WITH WeekendSales AS (
    SELECT 
        DATEPART(YEAR, SaleDate) AS SaleYear,
        DATEPART(MONTH, SaleDate) AS SaleMonth,
        SaleDate,
        CustomerID,
        ProductID,
        SaleAmount,
        DATENAME(WEEKDAY, SaleDate) AS WeekendDay
    FROM Sales
    WHERE DATENAME(WEEKDAY, SaleDate) IN ('Saturday', 'Sunday')
        AND SaleDate >= '2024-01-01'
),
MonthlyWeekendTotals AS (
    SELECT 
        SaleYear,
        SaleMonth,
        COUNT(DISTINCT CustomerID) AS UniqueWeekendCustomers,
        COUNT(*) AS WeekendTransactions,
        SUM(SaleAmount) AS TotalWeekendRevenue,
        AVG(SaleAmount) AS AvgWeekendTransaction
    FROM WeekendSales
    GROUP BY SaleYear, SaleMonth
)
SELECT 
    SaleYear,
    SaleMonth,
    DATENAME(MONTH, DATEFROMPARTS(SaleYear, SaleMonth, 1)) AS MonthName,
    UniqueWeekendCustomers,
    WeekendTransactions,
    FORMAT(TotalWeekendRevenue, 'C', 'en-US') AS FormattedRevenue,
    FORMAT(AvgWeekendTransaction, 'C', 'en-US') AS FormattedAvgTransaction
FROM MonthlyWeekendTotals
ORDER BY SaleYear, SaleMonth;

Method 3: Creating Reusable Weekend Functions

Comprehensive Weekend Utility Functions

For consistent weekend handling across applications, I recommend creating these utility functions:

-- Method 6: Create reusable weekend utility functions
-- Standard functions for weekend operations

-- Function 1: Check if date is weekend
CREATE FUNCTION dbo.IsWeekend(@Date DATE)
RETURNS BIT
AS
BEGIN
    RETURN CASE 
        WHEN DATENAME(WEEKDAY, @Date) IN ('Saturday', 'Sunday') 
        THEN 1 
        ELSE 0 
    END
END;

-- Function 2: Get weekend type
CREATE FUNCTION dbo.GetWeekendType(@Date DATE)
RETURNS VARCHAR(20)
AS
BEGIN
    RETURN CASE 
        WHEN DATENAME(WEEKDAY, @Date) = 'Saturday' THEN 'Saturday'
        WHEN DATENAME(WEEKDAY, @Date) = 'Sunday' THEN 'Sunday'
        ELSE 'Weekday'
    END
END;

-- Function 3: Get next weekend start (Saturday)
CREATE FUNCTION dbo.GetNextWeekendStart(@Date DATE)
RETURNS DATE
AS
BEGIN
    DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @Date)
    DECLARE @DaysToAdd INT
    
    SET @DaysToAdd = CASE 
        WHEN @DayOfWeek = 7 THEN 0  -- Already Saturday
        WHEN @DayOfWeek = 1 THEN 6  -- Sunday, add 6 days
        ELSE 7 - @DayOfWeek + 1     -- Other days
    END
    
    RETURN DATEADD(DAY, @DaysToAdd, @Date)
END;

-- Function 4: Count weekend days in date range
CREATE FUNCTION dbo.CountWeekendDays(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @WeekendCount INT = 0
    DECLARE @CurrentDate DATE = @StartDate
    
    WHILE @CurrentDate <= @EndDate
    BEGIN
        IF dbo.IsWeekend(@CurrentDate) = 1
            SET @WeekendCount = @WeekendCount + 1
        
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)
    END
    
    RETURN @WeekendCount
END;

After executing the above queries, I got the expected output as shown in the screenshots below.

How to Get Weekend Date in SQL
get weekend date in sql
How to Get Weekend Date SQL Server
how to find weekend date in sql server

Conclusion and Best Practices

After implementing weekend date handling solutions for many clients, I’ve learned that successful weekend date management in SQL Server requires a combination of technical expertise and business understanding.

Key Takeaways

• Use DATENAME() for universal compatibility: This approach works regardless of @@DATEFIRST settings, making your code more portable across different SQL Server environments.

• Create reusable utility functions: Standardizing weekend logic through custom functions ensures consistency across your applications and reduces maintenance overhead.

• Consider business context: Weekend handling requirements vary significantly between industries—retail companies may include weekend sales in regular analysis, while financial institutions often exclude them from business day calculations.

• Optimize for performance: Large-scale weekend analysis requires proper indexing strategies and query optimization, especially when dealing with historical data spanning multiple years.

• Plan for edge cases: Handle NULL dates, time zones, and international date formats appropriately to avoid unexpected results in production environments.

Recommended Implementation Strategy:

  1. Start with the DATENAME() approach for basic weekend detection
  2. Create utility functions for complex weekend calculations
  3. Implement proper indexing on date columns
  4. Test thoroughly with your organization’s specific DATEFIRST settings

You may also like the following articles