SQL Server DATETIME2FROMPARTS Function

In this article, we are going to explore the DateTime2FromParts function in SQL server. This function is very useful in such situations where we need to construct a datetime value using individual parts like year, month, day, hour, and minutes. Deep dive into this article to understand the use of this function with proper explanations along with examples.

Introduction to DateTime2FromParts

The SQL DateTime2FromParts function was introduced in the SQL server in 2012. It is a function used to construct a datetime2 value from a given individual date and various time segments. If you are not familiar datetime2 data type then go through the following points for better understanding. This function is mainly used when we have separate values of the exact time segments and we want to combine them to form a single datetime2 value.

• The datetime2 is a date and time data type in SQL server that has a higher precision than the older DATETIME type.

• It stores dates and times with up to 100 nanoseconds of precision.

• It takes 8 bytes of storage space as compared to normal DateTime which takes 4 bytes.

• It follows the following format to display the date and time YYYY-MM-DD HH:MM: SS.FFFFFFF.

• It supports a date range from January 1, 1753 to December 31 9999.

Syntax:

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Arguments:

The function accepts 8 arguments. Each of the argument is described below:

• Year: An integer value that specifies a year. Can be a 4-digit value like 2024.

• Month: An integer value between 1 and 12 that specifies a month.

• Day: An integer value between 1 and 31 that specifies a day.

• Hour: An integer value between 0 and 23 that specifies the hours.

• Minute: An integer value between 0 and 59 that specifies the minutes.

• Seconds: An integer value between 0 and 59 that specifies the seconds.

• Fractions: An integer value that specifies a fractional seconds value.

• Precision: An integer value between 0 and 7 that specifies the precision of the datetime2 value that DATETIME2FROMPARTS will return.

• The return type of the function is a datetime2 value.

Examples of DateTime2FromParts function in SQL Server 2012

Now, let us consider some practical applications of Datetime2fromparts function for a better understanding about of the working of the function.

1. Basic Usage without Precisions and Fractions

SELECT DateTime2FromParts(2023, 1, 15, 12, 30, 0, 0, 0) AS ConstructedDateTime;

This query constructs a datetime2 value for January 15, 2023, at 12:30 PM with no fractional seconds.

Output

2. Record the Exact Moment of Time

Suppose you want to store the exact time frame when your favourite football player scored the goal. Then you can run the below query for the same.

DECLARE @goalTime datetime2;

SET @goalTime = DATETIME2FROMPARTS(2023, 12, 31, 15, 37, 12, 500000, 6);

SELECT @goalTime AS "Time of Goal!";

Output:

Output

3. Capture Data with Microsecond Precision

Suppose we want to collect data from a 100m race and want to store the race finish time of each athlete with microsecond precision. Normal datetime data type won’t allow us to store the date and time with such precision. You can use the below format to store data with microsecond precision.

DECLARE @finishTime datetime2 = DATETIME2FROMPARTS(2023, 11, 19, 14, 32, 10, 254687, 6);
SELECT @finishTime AS "New World Record!";

Output:

4. Including Fractional Seconds

SELECT DateTime2FromParts(2023, 6, 8, 18, 45, 30, 9876543, 7) AS ConstructedDateTime;

We can also store fractional seconds with a precision value of 7 using the above query. The above query constructs a datetime2 value for June 8, 2023 at 6:45:30 PM with fractional seconds 9876543 and precision of 7 digits.

Output:

5. Handling Leap Year

SELECT DateTime2FromParts(2024, 2, 29, 10, 15, 30, 5000000, 7) AS ConstructedDateTime;

In this example, the query constructs a datetime2 value for February 29, 2024 (a leap year), at 10:15:30 AM with fractional seconds of 5000000 and a precision of 7.

Output:

Output

6. Error with Invalid Arguments

SELECT 
    DATETIME2FROMPARTS(2020, 13, 33, 11, 60, 59, 0, 0) result;

The datetime2fromparts function will generate an error even if a single argument passed to the function is not valid. In the above example we have passed the month parameter as 13 and the minute parameter as 60 which is not valid because the valid range for month is between 1 to 12 and valid range for minute is between 0 to 59. The output will be generated by the datetime2fromparts function only when all of the 8 arguments passed to the function are valid.

Output:

Output

Conclusion

In this article we have learned about the DATETIME2FROMPARTS function in SQL servers and its application in various practical use cases. Whenever we have separate time and date fragments and we want to generate a single datetime2 value we can use this function. However , it is important to note that if any of the arguments passed to the function are invalid then the function will generate an error so make sure that your arguments are valid before executing the function.