how to get monday from date in sql

Daniel Parker

3 min read

·

19-01-2025

1

0

Share

Determining the Monday of a given week is a common task in SQL, useful for reporting, scheduling, and data analysis. This article explores several methods to achieve this, catering to different SQL dialects and levels of complexity. We'll cover techniques for various database systems like MySQL, PostgreSQL, SQL Server, and Oracle.

Understanding the Problem

Before diving into the solutions, let's clearly define the problem. We need a SQL function or query that takes a date as input and returns the date of the Monday of the week containing that input date. For example, if the input date is a Wednesday, the output should be the preceding Monday. If the input is a Monday, the output should be the same date.

Methods to Extract the Monday

The approach to finding the Monday varies slightly depending on your specific SQL database system. Here are a few common methods:

Method 1: Using DATEPART and DATEADD (SQL Server)

SQL Server provides the DATEPART function to extract parts of a date (like day of the week) and DATEADD to add or subtract days. This method is efficient and straightforward:

CREATE FUNCTION GetMonday (@date DATE)
RETURNS DATE
AS
BEGIN
    DECLARE @dayOfWeek INT = DATEPART(weekday, @date);
    DECLARE @monday DATE = DATEADD(day, 2 - @dayOfWeek, @date);
    RETURN @monday;
END;

--Example Usage
SELECT dbo.GetMonday('2024-03-15'); -- Returns 2024-03-11 (Monday)

This function calculates the day of the week (@dayOfWeek), then adjusts the input date (@date) to find the preceding Monday using DATEADD. Note that SQL Server's DATEPART function considers Sunday as day 1, so we subtract @dayOfWeek - 2 to reach Monday.

Method 2: Using EXTRACT and INTERVAL (PostgreSQL)

PostgreSQL employs the EXTRACT function for date part extraction and INTERVAL for date adjustments. The approach is similar to the SQL Server method:

CREATE OR REPLACE FUNCTION get_monday(date_in DATE)
RETURNS DATE AS $
DECLARE
  day_of_week INT;
BEGIN
  day_of_week := EXTRACT(DOW FROM date_in);
  RETURN date_in - INTERVAL 'day' * (day_of_week); --PostgreSQL considers Monday as 0
END;
$ LANGUAGE plpgsql;


--Example Usage
SELECT get_monday('2024-03-15'); -- Returns 2024-03-11 (Monday)

PostgreSQL's EXTRACT(DOW FROM date) returns 0 for Monday, 1 for Tuesday, and so on. Therefore, subtracting day_of_week days directly gives us Monday.

Method 3: Using DAYOFWEEK and DATE_SUB (MySQL)

MySQL uses DAYOFWEEK and DATE_SUB:

DELIMITER //

CREATE FUNCTION GetMonday(input_date DATE) 
RETURNS DATE
DETERMINISTIC
BEGIN
    DECLARE day_of_week INT;
    SET day_of_week = DAYOFWEEK(input_date);
    RETURN DATE_SUB(input_date, INTERVAL (day_of_week - 2) DAY); -- MySQL considers Sunday as 1
END //

DELIMITER ;

--Example Usage
SELECT GetMonday('2024-03-15'); -- Returns 2024-03-11 (Monday)

Similar to SQL Server, MySQL's DAYOFWEEK function starts with Sunday as 1. We adjust accordingly to get the Monday.

Method 4: A More Generic Approach (Most SQL Dialects)

While the above methods are database-specific, we can create a more portable approach using modulo arithmetic:

-- This approach is adaptable to many SQL dialects, adjust day_of_week calculation if needed.
SELECT CASE 
           WHEN DAYOFWEEK(your_date) = 1 THEN your_date - INTERVAL 6 DAY -- Sunday
           WHEN DAYOFWEEK(your_date) = 2 THEN your_date --Monday
           ELSE your_date - INTERVAL (DAYOFWEEK(your_date) - 2) DAY 
       END AS monday_date
FROM your_table;

This method checks the day of the week and adjusts the date accordingly, making it more compatible across different database systems. You'll need to adapt the DAYOFWEEK function or equivalent for your specific system.

Choosing the Right Method

The best method depends on your specific SQL database system and your preferences. The database-specific functions are generally more efficient. The generic approach offers better portability but might require minor adjustments based on the database's day-of-week numbering. Remember to test thoroughly with various input dates to ensure accuracy.

Remember to replace your_date and your_table with your actual column and table names. This comprehensive guide provides several robust options for retrieving the Monday of any given date within your SQL environment.