SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

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

year Required. Specify the year of the datetime value.
month Required. Specify the month of the datetime value.
day Required. Specify the day of the datetime value.
hour Required. Specify the hours of the datetime value.
minute Required. Specify the minutes of the datetime value.
seconds Required. Specify the seconds of the datetime value.
fractions Required. Specify the fraction of the datetime value.
precision Required. Specify the precision of the datetime value to be returned.

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:

IDYearMonthDayHoursMinutesSeconds
11999323224555
220036853421
320101128142310
420048149819
5201211881118

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:

IDYearMonthDayHoursMinutesSecondsDATETIME2FROMPARTS_Value
119993232245551999-03-23 22:45:55.000
2200368534212003-06-08 05:34:21.000
3201011281423102010-11-28 14:23:10.000
4200481498192004-08-14 09:08:19.000
52012118811182012-01-18 08:11:18.000

❮ SQL Server Functions