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.
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:
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:
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:
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.