DATEFROMPARTS function

DATEFROMPARTS function is available in SQL Server since 2012 version.

DATEFROMPARTS function has 3 integer parameters which represent year, month and day respectively. The function returns date corresponding to  these parameters. It is convenient way forming a date when its components are storing separately or transmitting from a client.

For example,

SELECT DATEFROMPARTS(2017,5,25);

returns

To get the date corresponding to 25th day of current month you can use the following query

select DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 25);

Besides date, it is possible to construct values of TIME and DATETIME data types when using time components additionally to the date components. For example, the following query

SELECT TIMEFROMPARTS(9, 38, 59, 998, 7);

Pay attention to the last parameter which represents number of digits in the fractional part of a second. This integer parameter takes the value between 0 and 7 but can’t be less than number of digits in a presentation of the fractional part of a second.

For example, the query

SELECT TIMEFROMPARTS(9, 38, 59, 998, 3);

SELECT TIMEFROMPARTS(9, 38, 59, 998, 2);

Compare with

SELECT TIMEFROMPARTS(9, 38, 59, 098, 2), TIMEFROMPARTS(9, 38, 59, 098, 3);

The result is

The DATETIMEFROMPARTS function constructs a value of DATETIME data type. It takes 7 integer parameters namely year, month, day, hours, minutes, seconds, and milliseconds respectively. In so doing, milliseconds component is rounded to an accuracy of one of the following values: .000, .003, and .007.

SELECT DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 998),
DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 999),
DATETIMEFROMPARTS(2017, 5, 13, 9, 38, 59, 993);

The above query gives the following