SQL DATEFROMPARTS Function

by

In this SQL Server tutorial, I will explain the SQL DATEFROMPARTS function, which is a date and time-based function that allows us to manipulate date and time values.

Whether you are a developer or a student, this function is very helpful, as it constructs a new date from the specific parts of the date like day, year or month. Knowing about this function can make your tasks much easier when dealing with date values.

So here, I will explain the syntax of the DATEFROMPARTS() function with an example demonstrating how it works.

What is SQL DATEFROMPARTS Function?

The SQL DATEFROMPARTS function is a DateTime function that generates a new date value from the specified date components such as year, month, and day.

Simply put, pass the individual date parts like year, month and day to the SQL Server DATEFROMPARTS() function; it returns a new date by combining all the date parts into a single date value.

The syntax is given below.

DATEFROMPARTS(year, month, day);

Where,

  • DATEFROMPARTS(): The function that creates a new date value from the given date part value.
  • year: It is the year part of the date. It must be an integer value.
  • month: It is the month part of the date. It must be an integer expression.
  • day: It is the day part of the date. It must be an integer value.

Let’s understand the working of the DATEFROMPARTS() function in SQL Server through an example.

Creating Date using SQL DATEFROMPARTS Function

Consider that you have individual date parts such as year, month, and day; you want to create a date; for example, you have 2024 as year, 01 as month, and 20 as days.

To create a complete date from those parts of the date, look and run the query below.

SELECT DATEFROMPARTS(2024, 01, 20) AS Date;
Creating Date using SQL DATEFROMPARTS Function

The output shows that the SQL DATEFROMPARTS function created a new date value, ‘2024-01-20’, from the given separate date parts, such as 2024 as a year, 01 as a month, and 20 as days.

That’s how the DATEFROMPARTS () function works in SQL Server to generate a new date value from the specified individual date component.

But how are you going to use it in real life? For example, you can use it for handling user input; suppose you are a building where whenever users register, they provide their details such as name, age, phone number or DOB.

So maybe you are handling the backend part, too; you need to validate the incoming user inputs and store them in proper format;

For a moment, think that you need to create a function that takes the individual part of the user’s date of birth and creates a complete date value (DOB value).

Let me show you that the function stores the individual parts of the date of birth in a separate variable, shown below.

DECLARE @dob_year INT = 1999;

DECLARE @dob_month INT = 01;

DECLARE @dob_day INT = 15;

This is how you have declared a variable in a function, which stores the DOB info of the users; now, to create complete date from those variables containing the individual parts of the DOB, execute the query below and see.

SELECT DATEFROMPARTS(@dob_year, @dob_month, @dob_day) AS UserDOB;
Creating Date using SQL DATEFROMPARTS Function From Variables

As you can see in the result of the above picture, the DATEFROMPARTS() function created a complete DOB of the user, which is 1990-01-15, based on the format YYYY-MM-DD from the individual variables containing the date of birth parts.

That’s how you can also create a date value from the variables in SQL Server using the DATEFROMPARTS() function.

SQL DATEFROMPARTS Function Returns NULL

But you must ensure that you pass all the date parts values to the SQL DATEFROMPARTS function; if you miss the values, it returns a NULL value.

For example, pass the date parts value, year, and month, and keep the day part as NULL; look at the query below.

SELECT DATEFROMPARTS(2024, 01, NULL) AS CompleteDate;
SQL DATEFROMPARTS Function Returns NULL

The SQL DATEFROMPARTS function returns the NULL value, as you can see in the above query output, because one of the date part values is passed as NULL to the function.

  • So remember, whenever any of the three parts’ values is NULL, DATEFROMPARTS in SQL Server also returns the NULL value instead of the new date value.

Effects of Invalid Date Part on SQL DATEFROMPARTS Function

You need to ensure what kind of date part value you pass to the SQL DATEFROMPARTS function, which means passing valid or invalid date part values.

For example, if you pass the month as a negative value, the DATEFORMPARTS() function will raise an error. Look at the query below.

SELECT DATEFROMPARTS(2024, -1, 24) AS CompleteDate;
Effects of Invalid Date Part on SQL DATEFROMPARTS Function

So, after executing the above query, the DATEFROMPARTS() function returns the error which is ‘Cannot construct data type date, some of the arguments have values which are not valid.’

That means you have passed the invalid arguments (one of the date parts values) to the DATEFROMPARTS function in SQL Server.

That invalid value is the month value, -1, which is the negative value. Months can go from 1 to 12, but here, -1 is passed, which is out of range.

  • So, always pass the valid date parts to the DATEFROMPARTS SQL function to create a new date value.

So this is all about the DATEFROMPARTS() function in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how useful the SQL DATEFROMPARTS function is, which helps create new date values from the individual parts of the date.

With the help of examples you learned about, such as constructing date values, dealing with null values, and handling errors whenever any date part values are invalid.

From these examples, you now understand how to use this function effectively.

Next, you may like to read the following tutorials related to the DATEFROMPARTS() function: