You can use the T-SQL SMALLDATETIMEFROMPARTS() function in SQL Server to return a smalldatetime value from the various date/time parts.

This article provides examples of usage, as well as cases where you can get an error or null value.

Syntax

The syntax goes like this:

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Where each argument is an integer expression that specifies that particular part of the date/time.

Example

Here’s an example to demonstrate.

SELECT SMALLDATETIMEFROMPARTS( 2021, 05, 10, 23, 35 ) AS Result;

Result:

+---------------------+
| Result              |
|---------------------|
| 2021-05-10 23:35:00 |
+---------------------+

Invalid Arguments

You need to ensure all arguments are valid for their particular date/time part. If not you’ll receive an error.

SELECT SMALLDATETIMEFROMPARTS( 2021, 05, 60, 23, 35 ) AS Result;

Result:

Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

In this example, I gave the day argument a value of 60, which is too high.

Number of Arguments

You also need to ensure you provide the correct number of arguments (5). If not you’ll receive an error.

SELECT SMALLDATETIMEFROMPARTS( 2021, 05, 10 ) AS Result;

Result:

The smalldatetimefromparts function requires 5 argument(s).

Null Values

If any of the arguments are null, the result is NULL.

SELECT SMALLDATETIMEFROMPARTS( 2021, 05, NULL, 23, 35 ) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Remoting

Note that Microsoft advices that the SMALLDATETIMEFROMPARTS() function is capable of being remoted to SQL Server 2017 servers and above. It is not remoted to servers that have a version below SQL Server 2017.