Attach date and time SQL

  SELECT dateIntrvw, InterViewTime,   
  Cast( Format(dateIntrvw ,'yyyy-MM-dd') +' '+ Format(InterViewTime ,'HH:mm:ss.sss') as datetime)    
  FROM #temp;  

Hi,@srk

Try this:

select cast(dateadd(DAY, 0, datediff(DAY, 0, dateIntrvw)) AS datetime)  
 + cast(dateadd(DAY, 0 - datediff(DAY, 0, InterViewTime),InterViewTime) AS datetime)  
    from #temp  

Bert Zhou

 --DATETIMEFROMPARTS ( year, month, day, hour, minute, second, millisecond )  
 Select dateIntrvw,InterViewTime  
 , datetimefromparts(Year(dateIntrvw),month(dateIntrvw),day(dateIntrvw)  
 ,datepart(hour,InterViewTime),datepart(minute,InterViewTime),datepart(second,InterViewTime),datepart(millisecond,InterViewTime)  
 ) NewInterViewDateTime   
 from #temp  
  

Try this neat and simple solution:

create table #temp ( dateIntrvw datetime, InterViewTime datetime)   
      
 insert into #temp   
 select '2015-10-05 00:00:00.000','2015-10-04 09:30:00.000'  
 union all   
 select '2015-10-07 00:00:00.000','2015-10-05 16:00:00.000'  
  
 SELECT dateIntrvw, InterViewTime, dateIntrvw + CAST(CAST(InterViewTime AS TIME) AS DATETIME) AS InterviewDateTime FROM #temp;  

I find it more straight forward to use string formats.

 SELECT   
	TRY_CONVERT(datetime,CONCAT(CONVERT(varchar(25),dateIntrvw, 23),' ',CONVERT(varchar(25),InterViewTime, 114)), 121)  
 FROM #temp