DATETIME2FROMPARTS (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument.
Transact-SQL syntax conventions
Syntax
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
year
An integer expression that specifies a year.
month
An integer expression that specifies a month.
day
An integer expression that specifies a day.
hour
An integer expression that specifies the hours.
minute
An integer expression that specifies the minutes.
seconds
An integer expression that specifies the seconds.
fractions
An integer expression that specifies a fractional seconds value.
precision
An integer expression that specifies the precision of the datetime2 value that DATETIME2FROMPARTS
will return.
Return types
datetime2( precision )
Remarks
DATETIME2FROMPARTS
returns a fully initialized datetime2 value. DATETIME2FROMPARTS
will raise an error if at least one required argument has an invalid value. DATETIME2FROMPARTS
returns null if at least one required argument has a null value. However, if the precision argument has a null value, DATETIME2FROMPARTS
will raise an error.
The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIME2FROMPARTS
will raise an error.
This function is capable of being remoted to SQL Server 2012 (11.x) servers and above. It is not remoted to servers that have a version below SQL Server 2012 (11.x).
Examples
A. An example without fractions of a second
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;
Here is the result set.
Result
---------------------------
2010-12-31 23:59:59.0000000
(1 row(s) affected)
B. Example with fractions of a second
This example shows the use of the fractions and precision parameters:
When fractions has a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
When fractions has a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
When fractions has a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 5, 1 );
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 50, 2 );
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 500, 3 );
GO
Here is the result set.
----------------------
2011-08-15 14:23:44.5
(1 row(s) affected)
----------------------
2011-08-15 14:23:44.50
(1 row(s) affected)
----------------------
2011-08-15 14:23:44.500
(1 row(s) affected)