title: SQL Column Concatenation whilst keeping the datatype of first column
body: <p>I am creating a report for work, in which there is a certain assignment date. My work develops and maintains a program that works with these dates and displays DATETIME as a dropdown in the frontend. I will include pictures later.</p>
  <p>My solution for the problem now is to have two separate columns for the date and for the time of that assignment, however preferably I want to do it in one.</p>
  <p>This is my current code:</p>
  <pre><code>DECLARE @Column_3 INT = 1061
  
  SELECT 
      (SELECT TOP 1 t1.Column_1 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY t1.Column_4 DESC)  AS [Rand ID]
      , CASE  
          WHEN t2.Column_5 = '' then 'Not Used'
          ELSE t2.Column_5
          END AS [Strata ID]
      , 'Not Used' AS [Description]
      , t2.Column_6 AS [Treatment]
      , CASE t2.Column_6 
          WHEN 1 THEN 'Active'
          WHEN 2 THEN 'Placebo'
          END AS [Description]
      , CONVERT(DATETIME, (SELECT TOP 1 t1.Column_4 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 1) AS [Assignment Date]
      , RIGHT(CONVERT(NVARCHAR(24), (SELECT TOP 1 t1.Column_4 from Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 13), 12) AS [Assignment Time]
      , t5.Column_9 AS [Institute ID]
      , t3.Column_10 AS [Subject ID]
  
  
  FROM Table_2 t2 
   LEFT OUTER JOIN Table_3 t3 ON 
      t2.Column_2=t3.Column_2 AND 
      t2.Column_3=t3.Column_3
   LEFT OUTER JOIN Table_4 t4 ON
      t4.Column_3=t3.Column_3 AND 
      t4.Column_7=t3.Column_7
   LEFT OUTER JOIN Table_5 t5 ON 
      t5.Column_8 = t4.Column_8 AND 
      t5.Column_3 = t4.Column_3
  
  WHERE t2.Column_3=@Column_3 AND t2.Column_2 IS NOT NULL
  ORDER BY t2.Column_9 ASC
  </code></pre>
  <p>My question is primarily about the Assignment Date and Assignment Time.</p>
  <pre><code>, CONVERT(DATETIME, (SELECT TOP 1 t1.Column_4 FROM Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 1) AS [Assignment Date]
  , RIGHT(CONVERT(NVARCHAR(24), (SELECT TOP 1 t1.Column_4 from Table_1 t1 WHERE t1.Column_2=t3.Column_2 AND t1.Column_3=t3.Column_3 ORDER BY Column_4 DESC), 13), 12) AS [Assignment Time]
  </code></pre>
  <p>As you can see, I use two columns now. But if I concatenate the two columns, the Assignment Date changed formats completely.
  Besides that, in the frontend of the program it does not show up as a datepicker.</p>
  <p>Can I concatenate the two columns without losing the DATETIME datetype, functionalities + information displayed in the column?</p>
  <p><strong>This is what the results look like:</strong></p>
  <p><a href="https://i.stack.imgur.com/N4Pza.png" rel="nofollow noreferrer">Display of the result in Microsoft SQL.</a></p>
  <p><a href="https://i.stack.imgur.com/KR5x2.png" rel="nofollow noreferrer">Display of the result in the front end.</a></p>