SQL date functions with examples

In this article, we’ll learn how to handle dates using SQL. We’ll take a closer look at the main SQL date functions: v.number, EOMONTH, DATEADD, and DATEDIFF.

■ Real business example

Imagine you’re working for a digital marketing agency, XYZ Digital Solutions, that specializes in online advertising and campaign management. Your agency runs several digital marketing campaigns for various clients, each spanning multiple months. These campaigns are often billed on a monthly basis, with commissions earned based on the campaign’s performance across different time periods.

One of XYZ Digital Solutions’ key clients, “Luxe Fashion Boutique,” runs a promotional campaign for their new clothing line. The campaign is scheduled to run from November 1, 2023, to January 31, 2024. The total commission for the campaign is set at $9,000, depending on various performance metrics like clicks, impressions, and conversions.

The billing and commission structure agreed upon with Luxe Fashion Boutique involves a monthly breakdown. This means XYZ Digital Solutions needs to calculate the portion of the total commission earned each month, based on the campaign’s duration within that month. However, the campaign doesn’t align neatly with calendar months – it starts and ends mid-month. This makes the commission calculation for November, December, and January a bit complex.

XYZ Digital Solutions must provide Luxe Fashion Boutique with a detailed invoice at the end of each month, showing the commission earned for that specific month. The calculation needs to be precise and transparent, reflecting the actual days the campaign ran in each month.

For instance, the campaign runs 30 days in November, 31 days in December, and 31 days in January. The monthly commission isn’t simply a third of the total commission because the campaign doesn’t span an equal number of days each month.

To address this challenge, XYZ Digital Solutions uses SQL queries to accurately break down the campaign’s duration into individual months and calculate the commission for each month. This ensures that the billing is fair and corresponds to the actual campaign duration in each month.

By using the SQL query with functions like EOMONTH, DATEADD, and DATEDIFF, the agency can automatically calculate the exact number of days the campaign runs each month and determine the proportional commission. This automation saves time and reduces the potential for errors compared to manual calculations, especially when dealing with multiple campaigns.

■ SQL 날짜 관련 함수 (v.number, EOMONTH, DATEADD, DATEDIFF) 개념

v.number:
This isn’t a built-in SQL function but a common technique in SQL Server using the master.dbo.spt_values system table. It generates a series of numbers, often used for creating sequences or iterating over a range. In our context, it’s used to iterate over the months of a campaign.

INNER JOIN master.dbo.spt_values v 
ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end)

In the SQL code above, v.type = ‘P’ indicates the row used to create the set of numbers, meaning that the query orders the campaign start month (campaign_start) to the campaign end month (campaign_end) from zero. For example, if the campaign start month is November 2023 and the campaign end month is January 2024, v.number would look like this:

EOMONTH is used to get the last day of the month for a given date. This function can also calculate the end of the month on a date with a certain number of months remaining. For example, EOMONTH(‘2021-01-15’, 1) returns the last day of February 2021.

The DATEADD function adds or subtracts a specific unit of time (day, month, year, etc.) to a date. For example, DATEADD(month, 1, ‘2021-01-15’) adds a month to January 15, 2021, to return February 15, 2021.

DATEDIFF calculates the difference between two dates. For example, DATEDIFF(day, ‘2021-01-01’, ‘2021-01-31’) returns the difference in days between two dates, which is 30 days.

■ Let’s create sample SQL tables to practice!

To demonstrate the SQL query for breaking down a campaign’s duration into individual months and calculating the commission for each month, we’ll first need to create two example tables: campaign_meta and campaign_splits.

campaign_meta table contains the metadata for each marketing campaign.

• campaign_id: A unique identifier for each campaign.
• campaign_start: The start date of the campaign.
• campaign_end: The end date of the campaign.

campaign_splits table contains information about the financial splits of each campaign.

• site_id: A unique identifier for each site where the campaign is run.
• campaign_id: The campaign identifier, linking to campaign_meta
• commission: The total commission for the campaign.

-- Create campaign_meta
CREATE TABLE campaign_meta (
    campaign_id VARCHAR(50) PRIMARY KEY,
    campaign_start DATE,
    campaign_end DATE
);

-- Create campaign_splits
CREATE TABLE campaign_splits (
    site_id INT,
    campaign_id VARCHAR(50),
    commission DECIMAL(10, 2),
    FOREIGN KEY (campaign_id) REFERENCES campaign_meta(campaign_id)
);

-- Insert sample data into campaign_meta 
INSERT INTO campaign_meta (campaign_id, campaign_start, campaign_end) VALUES 
('CAMP123', '2023-11-01', '2024-01-31'),
('CAMP456', '2023-12-15', '2024-01-15');

-- Insert sample data into campaign_splits
INSERT INTO campaign_splits(site_id, campaign_id, commission) VALUES 
(101, 'CAMP123', 9000.00),
(102, 'CAMP456', 5000.00);

campaign_meta table contains two campaigns:

CAMP123 runs from November 1, 2023, to January 31, 2024.
CAMP456 runs from December 15, 2023, to January 15, 2024.

campaign_splits records the commissions:

For CAMP123 on site 101, the total commission is $9,000.
For CAMP456 on site 102, the total commission is $5,000.

■ SQL Solution

In this case, you’d have to go through a rather complicated calculation process that involves dividing the campaign duration into individual months and calculating the commission for each month proportionally. In this case, it’s a good idea to create a CTE for each monthly period. Create the following table in SQL (the answer is below, but you might want to practice first).

SELECT 
        b.site_id,
        a.campaign_id,
        a.campaign_start,
        a.campaign_end,
        b.commission,
        CASE 
            WHEN v.number = 0 THEN a.campaign_start 
            ELSE DATEADD(DAY, 1, EOMONTH(a.campaign_start, v.number - 1))
        END as month_start,
        EOMONTH(a.campaign_start, v.number) as month_end,
        v.number as month_number
    FROM 
        campaign_meta as a
    INNER JOIN 
        campaign_splits as b ON a.campaign_id = b.campaign_id
    INNER JOIN 
        master.dbo.spt_values v ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end);

Now let’s use that CTE to calculate the month of commission (month_of_commission), the days in the campaign for that month (days_in_month), and the commission for that month (monthly_commission). The end result should look like this:

WITH MonthlyPeriods AS (
    SELECT 
        b.site_id,
        a.campaign_id,
        a.campaign_start,
        a.campaign_end,
        b.commission,
        CASE 
            WHEN v.number = 0 THEN a.campaign_start 
            ELSE DATEADD(DAY, 1, EOMONTH(a.campaign_start, v.number - 1))
        END as month_start,
        EOMONTH(a.campaign_start, v.number) as month_end,
        v.number as month_number
    FROM 
        campaign_meta as a
    INNER JOIN 
        campaign_splits as b ON a.campaign_id = b.campaign_id
    INNER JOIN 
        master.dbo.spt_values v ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(MONTH, a.campaign_start, a.campaign_end)
)
SELECT 
    site_id,
    campaign_id,
    campaign_start,
    campaign_end,
    commission,
    FORMAT(month_start, 'yyyy-MM') as month_of_commission,
    DATEDIFF(DAY, 
        CASE WHEN month_start < campaign_start THEN campaign_start ELSE month_start END,
        CASE WHEN month_end > campaign_end THEN campaign_end ELSE month_end END
    ) + 1 as days_in_month,
    monthly_commission
FROM 
    (SELECT *, 
        (commission / (DATEDIFF(DAY, campaign_start, campaign_end) + 1)) * 
        (DATEDIFF(DAY, 
            CASE WHEN month_start < campaign_start THEN campaign_start ELSE month_start END,
            CASE WHEN month_end > campaign_end THEN campaign_end ELSE month_end END
        ) + 1) as monthly_commission
    FROM MonthlyPeriods) as MonthlyCommissions;

In this article, we looked at the date functions in MSSQL and how to use them in real-world applications. Functions like DATEADD, EOMONTH, and DATEDIFF are essential for efficiently handling date data in a database, which makes data management and analysis easier.