Today we will discuss a simple yet powerful function in SQL Server called DATETIMEFROMPARTS(). This function is often overlooked, but it can be very useful when it comes to building DateTime values. In many cases, developers rely on converting strings to DateTime, but DATETIMEFROMPARTS() provides a more efficient and straightforward way to create DateTime values.

The DATETIMEFROMPARTS() function accepts seven arguments, each representing a different part of the date and time. By providing the necessary values, the function constructs a valid DateTime value. Let’s take a look at the syntax:

DATETIMEFROMPARTS(year, month, day, hour, minute, second, millisecond)

When you pass valid date and time values to this function, it will generate a DateTime value based on the provided parts. Let’s see a few examples:

SELECT DATETIMEFROMPARTS(2019, 02, 03, 11, 12, 13, 557) AS DateTime;

When we execute the above script, we get the following result:

DateTime
-----------------------
2019-02-03 11:12:13.557

It’s important to note that the precision of the DateTime value is 0.00333 seconds. Therefore, when you pass values with more precision, the function will automatically round up or down to the nearest DateTime value.

If you pass incorrect values, the function will throw an error. It’s crucial to provide valid values for each part of the DateTime.

Additionally, if any of the values passed to the function are NULL, the entire result set will also be NULL.

SQL Server also provides two other similar functions: DATEFROMPARTS and DATETIME2FROMPARTS. These functions allow you to build different date and DateTime parts based on your requirements.

By utilizing DATETIMEFROMPARTS() and its related functions, you can easily create and manipulate DateTime values in SQL Server.

For more information on related topics, you can check out the following blog posts:

• Adding Datetime and Time Values Using Variables
• How to Trim TIME Part in DATETIME Values? – Interview Question of the Week #200
• Difference Between DATETIME and DATETIME2
• MySQL – When to Use TIMESTAMP or DATETIME – Difference Between TIMESTAMP or DATETIME
• Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video