SQL DATEFROMPARTS() Function

SQL DATEFROMPARTS()

The SQL DATEFROMPARTS function is used to construct a date from individual year, month, and day values. This function returns a date value in the format YYYY-MM-DD and is particularly useful when dealing with separated date components.

In this tutorial, we will explore the DATEFROMPARTS function, its syntax, and practical examples demonstrating its usage.

---

Syntax of SQL DATEFROMPARTS Function

The basic syntax of the DATEFROMPARTS function is:

DATEFROMPARTS(year, month, day)

Parameters:

• year – The four-digit year value (e.g., 2025).
• month – The month value (1 to 12).
• day – The day value (1 to 31, depending on the month).

Returns: A date in the format YYYY-MM-DD.

---

Step-by-Step Examples of SQL DATEFROMPARTS

1. Generating a Specific Date

Let’s generate a date for January 26, 2025, using the DATEFROMPARTS function.

SELECT DATEFROMPARTS(2025, 1, 26) AS generated_date;

Output:

Explanation:

• The function combines the provided year (2025), month (1), and day (26) to return 2025-01-26.
• This is useful when constructing dates dynamically in queries.

---

2. Creating a Birthdate Column in a Query

Suppose we have a persons table where birth year, month, and day are stored in separate columns. We can use DATEFROMPARTS to create a proper birthdate.

In SQL Server:

CREATE TABLE persons (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50),
    birth_year INT,
    birth_month INT,
    birth_day INT
);

Insert sample data:

INSERT INTO persons (name, birth_year, birth_month, birth_day)
VALUES 
('Arjun', 1992, 5, 14),
('Priya', 1998, 8, 22),
('Ram', 1985, 12, 5);

Now, let’s construct the full birthdate for each person:

SELECT name, 
       birth_year, 
       birth_month, 
       birth_day, 
       DATEFROMPARTS(birth_year, birth_month, birth_day) AS birthdate
FROM persons;

Output:

Explanation:

• The query retrieves each person’s birth year, month, and day.
• The DATEFROMPARTS function constructs the full date from these components.
• The result provides a structured birthdate column.

---

Conclusion

The SQL DATEFROMPARTS() function is useful for constructing date values from separate components. In this tutorial, we covered:

1. The syntax of DATEFROMPARTS() function, and how it combines year, month, and day.
2. Generating specific dates dynamically in queries.
3. Using DATEFROMPARTS to format birthdates in a table.