SQL Server DATETIME2FROMPARTS() Function

The SQL Server (Transact-SQL) DATETIME2FROMPARTS() function returns a datetime value from the specified date and time arguments with specified precision.

The DATETIME2FROMPARTS() function returns NULL if any of the argument has a null value. However, if the precision argument is null, then an error is raised. For invalid arguments, an error will be raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Syntax

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

Parameters

Return Value

Returns the datetime value from the specified parts.

Example 1:

The example below shows the usage of DATETIME2FROMPARTS() function.

SELECT DATETIME2FROMPARTS(1999, 10, 25, 22, 45, 58, 0, 0);
Result: '1999-10-25 22:45:58.0000000'

SELECT DATETIME2FROMPARTS(1999, 10, 25, 22, 45, 58, 4, 1);
Result: '1999-10-25 22:45:58.4'

SELECT DATETIME2FROMPARTS(1999, 10, 25, 22, 45, 58, 40, 2);
Result: '1999-10-25 22:45:58.40'

SELECT DATETIME2FROMPARTS(1999, 10, 25, 22, 45, 58, 400, 3);
Result: '1999-10-25 22:45:58.400'

Example 2:

Consider a database table called DateTimeTable with the following records:

The following statement can be used to get the datetime value using the records of various columns of this table.

SELECT *, DATETIME2FROMPARTS(Year, Month, Day, Hours, Minutes, Seconds, 0, 3) 
AS DATETIME2FROMPARTS_Value FROM DateTimeTable;

This will produce the result as shown below: