DATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a DATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

WHERE: year, month, day, hour, minute, seconds and milliseconds are integer expressions representing valid year, month, day, hour, minute, seconds and milliseconds respectively.
Return Type: DATETIME

Example 1: DATETIMEFROMPARTS – with valid date and time part integer constants

SELECT
 DATETIMEFROMPARTS(2013,6,23,1,20,14,8) AS 'DATETIME',
 DATETIMEFROMPARTS(1753,1,1,0,0,0,0) AS 'MIN DATETIME',
 DATETIMEFROMPARTS(9999,12,31,23,59,59,997) AS 'MAX DATETIME'

Result:

DATETIME                               MIN DATETIME                           MAX DATETIME
———————–                      ———————–                        ———————–
2013-06-23 01:20:14.007       1753-01-01 00:00:00.000        9999-12-31 23:59:59.997

Example 2: DATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:

DATETIME
———————–
2013-06-23 01:20:14.877

Example 3: DATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @invalidmonth, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
Msg 289, Level 16, State 3, Line 3
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example 4: DATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = NULL, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
DATETIME
———————–
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012: