SQL Server DATEFROMPARTS()

SQL Server DATEFROMPARTS() Function

The SQL Server DATEFROMPARTS() function returns a date value for the specified year, month, and day. This function is useful for constructing date values from individual date parts.

Syntax

SELECT DATEFROMPARTS(year, month, day);

The DATEFROMPARTS() function takes three arguments:

  • year: The year value for the date.
  • month: The month value for the date.
  • day: The day value for the date.

Example SQL Server DATEFROMPARTS() Function Queries

Let's look at some examples of SQL Server DATEFROMPARTS() function queries:

1. Basic DATEFROMPARTS() Example

SELECT DATEFROMPARTS(2024, 6, 1) AS date_value;

This query constructs a date value from the year 2024, month 6, and day 1. The result will be:

date_value
----------
2024-06-01

2. DATEFROMPARTS() with Variables

DECLARE @year INT = 2024, @month INT = 12, @day INT = 25;
SELECT DATEFROMPARTS(@year, @month, @day) AS date_value;

This query constructs a date value from variables storing the year, month, and day. The result will be:

date_value
----------
2024-12-25

3. DATEFROMPARTS() in a Table

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_year INT,
    event_month INT,
    event_day INT
);
INSERT INTO events (id, event_name, event_year, event_month, event_day) VALUES (1, 'Event 1', 2024, 6, 1);
INSERT INTO events (id, event_name, event_year, event_month, event_day) VALUES (2, 'Event 2', 2024, 12, 25);
SELECT id, event_name, DATEFROMPARTS(event_year, event_month, event_day) AS event_date
FROM events;

This query constructs date values from the year, month, and day columns in the events table. The result will be:

id  event_name  event_date
--- ----------- ----------
1   Event 1     2024-06-01
2   Event 2     2024-12-25

4. DATEFROMPARTS() with a JOIN

CREATE TABLE holidays (
    holiday_id INT PRIMARY KEY,
    holiday_name VARCHAR(255),
    holiday_year INT,
    holiday_month INT,
    holiday_day INT
);
INSERT INTO holidays (holiday_id, holiday_name, holiday_year, holiday_month, holiday_day) VALUES (1, 'New Year', 2024, 1, 1);
INSERT INTO holidays (holiday_id, holiday_name, holiday_year, holiday_month, holiday_day) VALUES (2, 'Christmas', 2024, 12, 25);
SELECT e.event_name, h.holiday_name, DATEFROMPARTS(h.holiday_year, h.holiday_month, h.holiday_day) AS holiday_date
FROM events e
JOIN holidays h ON e.event_year = h.holiday_year;

This query constructs date values from the year, month, and day columns in the holidays table and joins them with the events table based on the year. The result will be:

event_name  holiday_name  holiday_date
----------- ------------- ------------
Event 1     New Year      2024-01-01
Event 1     Christmas     2024-12-25
Event 2     New Year      2024-01-01
Event 2     Christmas     2024-12-25

Conclusion

The SQL Server DATEFROMPARTS() function is a powerful tool for constructing date values from individual date parts. Understanding how to use the DATEFROMPARTS() function and its syntax is essential for effective date manipulation and data processing in SQL Server.