Combining dateparts to create new datetime in SQL Server

Question

I am trying to create my own date by combining dateparts of different columns. My code is below:

CAST((datepart(yy,[FIRST_INSTALL_DATE]) 
     + '-'
     + datepart(mm,[INSTALL_DATE]) 
     + '-'
     + datepart(dd,[INSTALL_DATE])) as Datetime) AS [Install_Date],

(Where FIRST_INSTALL_DATE's value is 2014-01-01 and INSTALL_DATE's value is 1900-08-07.)

However, I get the output below where it is supposed to be 2014-08-07 00:00:00.000

1905-08-07 00:00:00.000

I appreciate any idea. Thanks.

Answer 1

Basically you are trying to add numbers,

2014 +- 03 +- 02

You must convert every DATEPART to VARCHAR before to concat it.

CAST(DATEPART(YY, FIRST_INSTALL_DATE) AS varchar(10)) 

SQL-Server 2012

DECLARE @FIRST_INSTALL_DATE datetime = '2016/01/01 00:00:00';
DECLARE @INSTALL_DATE datetime = '2017/02/03 00:00:00';


SELECT DATEFROMPARTS (datepart(yyyy, @FIRST_INSTALL_DATE), 
                      datepart(MM,@INSTALL_DATE), 
                      datepart(dd,@INSTALL_DATE)) AS InstallDate;

GO

| InstallDate         |
| :------------------ |
| 03/02/2016 00:00:00 |

dbfiddle here