TIMEFROMPARTS Function in SQL Server

In this SQL Server tutorial, I will explain the TIMEFROMPARTS function in SQL Server, which constructs time data from the given time parts.

As you know, dealing with precise time in a database is very necessary. To create time value with high precision, use this TIMEFROMPARTS function in SQL Server.

You will learn about ‘What is TIMEFRMPARTS function?’ with its syntax. Then, with the help of an example, you will learn to create a time value. After that, you will see what happens when a NULL value is passed to this function.

Finally, you will learn how to use the TIMEFROMPARTS() function on the table to generate a time value from the table columns.

Table of Contents

TIMEFROMPARTS Function in SQL Server

The TIMEFROMPARTS function in SQL Server creates a new time value from the specified parts of the time, such as hours, minutes, seconds, fractions of seconds, and precision.

If you have time components such as 2 hours, 4 minutes, and 33 seconds, you can pass this component to the TIMEFRMPARTS function; this function constructs the complete time value such as ’02:04:33′.

The syntax is given below.

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Where,

TIMEFOMRPARTS(): The function creates a time value from time parts. It accepts five parameters or values of type integer.
hour: It represents the time of the day and can be from 1 to 24.
minute: It represents the part of the time and can be from 1 to 60
seconds: It represents the second section of the time value and can be from 1 to 60.
fractions: It represent the fraction second of the time value.
precision: It represents the precision of the fraction part of seconds.

The return type of the TIMEFROMPARTS() function is time(precision).

Let’s see an example of how to generate a time value from the time components.

Suppose you have time components (parts) such as 10, 23, 20, 0,0. To create a time value from these components, use the TIMEFROMPARTS function in SQL Server, as shown below.

SELECT TIMEFROMPARTS(10, 23, 20, 0, 0) AS TimeValue;

Looking at the query’s output, time value 10:23:20 with format is created using the TIMEFROMPARTS() function, so to this function, we have passed the five values, which are time parts.

So, above is the time value without a fraction of a second, only containing the hour, minutes and seconds parts. Even this function formatted the value like separating each part of the time using the colon (:).

TIMEFROMPARTS Function in SQL Server with Fraction of Seconds

To include the fraction of the second in the time value you want to create, use the last two parameters of the TIMEFROMPARTS function in SQL Server.

But here, you need to know how to use the two parameters, fractions and precision.

When you specify the fractions as 4, specify the precision as 1.
When you specify the reactions as 40, specify the precision as 2.
When you specify the fractions as 400, specify the precision as 3.
When you specify the fractions as 4000, specify the precision as 4.

For example, you have time components as 23, 12, 44, 60, and 2, and to create a time value with a fraction of a second, run the query below.

SELECT TIMEFROMPARTS(23, 12, 44, 60, 2) AS TimeValue;

TIMEFROMPARTS Function in SQL Server with Fraction of Seconds

From the output, the time value also contains the fraction of seconds, which is 60. Here in the TIMEFORMPARTS() function, the fraction is 50, and the precision is 2, which means a number of digits to represent the fraction part.

It raises an error if you specify the precision part as 1 and the fraction part as 60. For example, execute the query below.

SELECT TIMEFROMPARTS(23, 12, 44, 60, 1) AS TimeValue;

TIMEFROMPARTS Function in SQL Server with Fraction of Seconds Raises Error

Specifying the precision as 1 and the fraction part as 60 raises an error you can see in the above output.

To fix that error, you need to specify the precision as 2.

This is how to generate a time value with a fraction of a second using the TIMEFROMPARTS function in SQL Server.

TIMEFROMPARTS Function in SQL Server with NULL

If you pass the NULL value to the TIMEFROMPARTS function in SQL Server, it returns the null. Also, if any of the five parameters is null, it returns a null.

Let’s check with NULL values, as shown in the query below.

SELECT TIMEFROMPARTS(NULL, 30, NULL, NULL, 1) AS TimeValue;

TIMEFROMPARTS Function in SQL Server with Null

As you can see, it returns output when you pass any of the parameters as NULL to the TIMEFROMPARTS() function.

This is how the TIMFROMPARTS function responds when a NULL value is passed.

Using TIMEFROMPARTS Function in SQL Server on Table

Let me show you how to use the TIMEFROMPARTS function on table data. For example, suppose you have an EvenSchedule table, shown below.

The table contains columns EventID, EventName, Hour, Minute Second Fraction, and the even time, separated into different time components.

You must combine the hour, minute, second, and fraction into a single time value. For that, use the TIMEFROMPARTS() function, as shown below.

SELECT
	EventID,
	EventName,
	TIMEFROMPARTS(Hour, Minute, Second, Fraction, 0) AS EventTime
FROM
	EventSchedule;

Using TIMEFROMPARTS Function in SQL Server on EventSchedule Table

Look at the query output; it contains the EventName and EventTime columns, representing each event name with timing.

Here, the column EventTime in the result set combines time component values in columns Hour, Minute, Second, Fraction, and 0, the precision value passed.

So if the time components exist in table columns, then you can pass the columns to the TIMEFROMPARTS function in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to create a time value using the TIMEFROMPARTS function in SQL Server and create a time value from different time components such as hour, minute, seconds, fractions and precision.

Additionally, you passed the NULL value instead of time components to the TIMEFROMPARTS() function, which returned a NULL value.

Also, you have used the TIMEFROMPARTS() function on the EventSchedul table and constructed time values from the time components existing in the different columns of the table.