We can get a date by combining the values such as year, month and day with the help of DATEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS functions.
DATEFROMPARTS:
It takes year, month and day values as parameters and returns a value in the date data type.
Sample Usage:
1 | SELECT DATEFROMPARTS ( 2019, 02, 16 ) AS date_result; |
DATETIMEFROMPARTS:
It takes year, month, day, hour, minute, seconds, and milliseconds values as parameters and returns a value in the datetime data type.
Sample Usage:
1 | SELECT DATETIMEFROMPARTS ( 2019, 02, 16,23,59,59,0 ) AS datetime_result; |
DATETIME2FROMPARTS:
It takes year, month, day, hour, minute, seconds, fractions and precision values as parameters and returns a value in the datetime2 data type.
Sample Usage:
1 2 3 4 | SELECT DATETIME2FROMPARTS ( 2019, 02, 16,23,59,59,0,0 ) AS datetime2_result, DATETIME2FROMPARTS ( 2019, 02, 16,23,59,59,2,1 ) AS datetime2_result, DATETIME2FROMPARTS ( 2019, 02, 16,23,59,59,20,2 ) AS datetime2_result, DATETIME2FROMPARTS ( 2019, 02, 16,23,59,59,200,3 ) AS datetime2_result |
If you try to use it as follows, you will receive the below error:
1 | SELECT DATETIME2FROMPARTS ( 2019, 02, 16,23,59,59,200,1 ) AS datetime2_result |
Msg 289, Level 16, State 5, Line 1
Cannot construct data type datetime2, some of the arguments have values which are not valid.
SMALLDATETIMEFROMPARTS:
It takes year, month, day, hour and minute values as parameters and returns a value in the smalldatetime data type.
Sample Usage:
1 | SELECT SMALLDATETIMEFROMPARTS( 2019, 02, 16,23,59) AS smalldatetime_result |